保存ID值，但将数据恢复到报表或表单中 [英] Saving ID values, but getting the data back in reports or forms

问题描述

我有一个包含ID和附件名称字段的表。我正在尝试创建一个查询，将ID从一个comboxbox存储到一个名为customer product的表。

AccessoryID1 AccessoryID2 .... AccessoryID12

在我的报告中我想要显示客户产品所带来的所有配件。

但是在客户产品表中，只保存了ID，我无法得到该名称。如果我使用客户产品表中的ID字段和附件表中的名称进行查询，我一次只能使其用于1个附件，但是在报表上我需要1个查询才能将它们全部合并。由于我想显示名称而不是ID，因此字段名称与我想要使用附件名称12次相同，记住有些可能是空白值，因为有些产品配件少于其他产品。

我试过附件1：Iif（IsNull（[附件名称]），"，[附件名称]，但只有在我有1个附件时才有效。

希望我有对此事做了一些帮助，解释得很清楚

I have a table with ID and Accessory Name fields. I am trying to create a query the ID is stored from a comboxbox to a table called customer product.
AccessoryID1 AccessoryID2 .... AccessoryID12
On my report I would like to show all of the accessories that came in with the customer''s product.
But as in the customer product table the ID is only saved I cannot get the name to come up. If I make a query with the ID field from the customer product table and the name from the Accessories table I can only get it to work for 1 Accessory at a time, but on a report I need 1 query to combine them all. As I want to display the Name not the ID, the field names would be the same as I want to use the Accessory Name 12 times, remembering that some might be blank values as some products come with less accessories than others.
I tried Accessory 1: Iif(IsNull([Accessory Name]), "", [Accessory Name]), but only works if I have 1 accessory.
Hopefully I have explained it well enough for some help on the matter

推荐答案

UltimateNeo24，


欢迎来到字节！


您的问题有点不清楚，但很明显足以证明您应该重新考虑报告（和表格）的结构。看来你有多个字段？附件？，对吗？相反，您可能希望有一个与您的订单相关的表（例如tblOrderAccessories），它使用订单中的ID和附件中的ID，这样订单可以包含任意数量的附件（不仅仅是最多12个） ）。


然后，您的表格可以有一个连续表格样式的子表格，您可以添加附件;该报告将以类似的方式构建一个子报告。


之后，当您将附件表加入到您的附件中时，显示名称的问题将更容易解决tblOrderAccessories表并拉出附件名称而不是ID。


我希望这是有道理的，但这是DB设计中标准的，经过验证的真实实践和原则。如果您愿意，我们可以提供更多指导，或者如果您正在努力设计，我们也可以帮助您。


希望这个hepps！

UltimateNeo24,

Welcome to Bytes!

Your question is a bit unclear, but it is clear enough to demonstrate that you should rather rethink the structure of your Report (and Form). It appears that you have multiple fields for ?Accessories?, correct? Rather, you would want to have a table related to your orders (for example tblOrderAccessories) which uses the ID from the Order and the ID from Accessories, such that an order can have as many accessories as they wish (not just a maximum of 12).

Then, your Form can have a sub form in Continuous Forms style that you can add accessories; the report would have a sub report built in a similar fashion.

After that, your issue of displaying the names will be much more easy to tackle, as you join the Accessories table to your tblOrderAccessories Table and pull the Accessory Name instead of the ID.

I hope this makes sense, but this is a standard, tried and true practice and principle in DB design. We can provide more guidance if you wish, or if you are struggling with the design, we can help you there, too.

Hope this hepps!

你好Twinnyfo，谢谢你回复我的帖子。我的表格结构有问题。我有一个表客户和一个表Customer_Product。我也有表制造商，产品和型号。制造商有许多不同的产品和相同产品的许多不同型号。所以我使用查找制作了一个表格，将制造商加入到产品和模型中。从产品我还有一个配有这些类型opf产品的配件表。所有这些选择都使用ID字段保存到客户产品表中。不要保存占用空间的多个名字。然而，由于文本框没有组合框所具有的选项，我正努力让名称退出以显示在表单或报表上。我添加了一个指向该文件的链接以及可能有用的关系图像。感谢您的帮助 https://drive.google.com/open ？id = 1T -... Ef8HbvLCQ_1aYS

Hello Twinnyfo, thank you for replying to my post. I am having trouble with my table structure. I have a table customers and a table Customer_Product. I also have tables Manufacturer, product, and Model. The manufacturer has many different products and many different models of the same product. So i made a table using the lookup to join the manufacturers to the products and models. From the Products i have also a table of Accessories that come with those type opf products. All these selections are saved into the customer Products table using the ID fields. As not to save multilple names that take up space. I am however struggling to get the names back out to display on a form or report as the textboxes dont have the options that a combobox has. I added a link to the file and the image of relationships that might help. Thank you for your help https://drive.google.com/open?id=1T-...Ef8HbvLCQ_1aYS

我看不到您的图像。发布图片时使用高级按钮上传图片。


但是，问题的答案非常简单。如果表具有正确的关系，则在为报表构建查询时，请使用表中的文本值而不是ID。因此，如果Accessories表连接到Products表，而不是从Products表中提取ID字段，请从附件表中提取AccessoryName。


此外，对于表单，如果你想要一个组合框，你总是可以在表单中添加一个组合框，但是从附件表中指定一个查询以包含ID和AccessoryName。你使用ID作为绑定列。


希望这是有道理的。

I can?t see your image. Use the Advanced button when you post to upload an image.

However, the answer to your problem is very simple. If your tables have proper relationships, when you build the query for your report, instead of the ID, use the text value from the table. So, if the Accessories table is joined to the Products table, instead of pulling the ID field from the Products table, pull the AccessoryName from the Accessories Table.

Also, for forms, if you want a Combo Box, you can always add a combo box to a form, but designate a query from the Accessories table to include the ID and the AccessoryName. You use the ID as the bound column.

Hope this makes sense.

这篇关于保存ID值，但将数据恢复到报表或表单中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！