Saving ID values, but getting the data back in reports or forms

[UltimateNeo24]

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

[twinnyfo]

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 tblOrderAccesso ries) 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 tblOrderAccesso ries 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!

[UltimateNeo24]

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_Produc t. 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

[twinnyfo]

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.

[UltimateNeo24]

I have attached the image of relationships, hope that helps
[imgnothumb]https://bytes.com/attachments/attachment/9819d1544993015/relationships.j pg[/imgnothumb]

[twinnyfo]

Neo,

It is a little unclear from your relationships if "Customer Product" is a table for invoices that lists your sales to customers or if it is something else. For this response, I am going to go with that assumption--so this may be wasted time.

First, some advice, in naming Tables (and other objects) it is usually wise to avoid names with spaces. Additionally, you should think about adding a prefix to your objects so that you avoid duplicating object names, which can confuse the DB at times. For example a Table named Customers could get confused with a Form or Report named Customers. Thus tblCustomers is readily distinguished from frmCustomers and rptCustomers. You can do a Google search for Database Naming Conventions--pick one you like and stick with it.

On the surface, it looks like your Tables Manufacturer Name and Manufacturer Product are related correclty, but if the only field in Product Name is the Product Name, then you ought to simply add that field to Manufacturer Product. Then use the ProductID ad the FK for Accessories as you have it now.

If Customer Product is for invoices, it may be wise to call it that (tblInvoices). Then, have a separate Table for the products ordered on those Invoices (tblInvoiceProdu cts). That Table would have a Foreign Key to ProductID. Then, one more Table that lists all the Accessories for each Product ordered (tblInvProdAcces sories). This table would have an FK to Accessories.ID.

Although this seems like a lot of extra work, when dealing with Databases, this little extra work actually reduces work over the long haul because your database is more dynamic and can be easily kept up to date with changing products and accessories and changing your product line is as simple as adding records to a table, instead of reworking the structure of your tables, forms and reports.

Hope this hepps! Let us know how you get along with this.

[Rabbit]

It's probably wrong that accessories 2 id is joined to accessories name

[twinnyfo]

Yes - although not expressly stated in my post, Accessories 2 probably shouldn't even exist.

[UltimateNeo24]

Maybe this will help. I tried what you said but then i get duplicate values.

The idea is that a customer books in there product. With the products there are many manufaturers that do same products but different model names. The accessories are called the same for different product types. E.g. Delonghi do various models of Automatic coffee machines but also do Manual Coffee machines. Sunbeam do Large Manual Coffee Machines, Automatic Coffee Machines, Blenders, Irons, Small Manual Coffee Machines.

The Accessories are going to be the same for each manufacturer of the same product E.g. Automatic Coffee Machines all have a grounds container and water tank. So what i am trying to do is a cascading combobox on a form that is dependant Manufacturer then product then model. From the selection of the product it will provide another set of comboboxes for the Accessories of that product. As 1 Customer can have many products or come in many times with same machine. I only want to have the customer details once. This is a database for repairs to products customers bring in. Eventaully after this booking in section i will have to create a quote section which quotes the parts and labour for the job. What i am trying to do is record what the customer brings in as 1 record from tables that have the Products, Accessories and Parts (Although not a complete list) that are in the database already, with room to add more products, accessories and parts. I really want to store IDs but get the names back into reprots and forms.

[twinnyfo]

Your explanation is helpful.

Some information on cascade filtering forms based upon combo boxes can be found in these two articles: Here and here.

Perhaps this description might clarify the construct that I am recommending.

When a customer comes in, they may be a repeat customer, or a new customer. Either way, that customer's information is in the Customers Table. They have an item (or items) for repair. This customer's "single visit" will have one invoice. Each invoice uses the Customer ID as a foreign key back to the Customers Table.

Each invoice may contain one item or it may contain several items. Each of these items would be stored in a Table (for example, tblInvoiceItems). This table would use the InvoiceID as a Foreign Key back to the Invoices Table. You would not want to simply say 2 x Coffee machines unless those two coffee machines were identical (same make, model, and accessories).

Each Invoice Item would have a list of Accessories in the Accessories Table, using the InvoiceItemID as a foreign Key back to the Invoice Items Table.

Now, although this is a bit complex, Your Accessories Table will have to contain enough information so that you can choose any and all of the Accessories for each make, device type and model of device (so, you will probably want to have a Table for Device Types as well: tblDeviceTypes.

Your Accessories Table would look similar to this:

[B]tblAccessories[/B]

[B][U]Field[/U][/B]      [B][U]Type[/U][/B]
ID         AutoNumber
MfgID      FK to tblManufacturers
DevTypeID  FK to tblDeviceTypes
ProdID     FK to tblProducts
Accessory  Type of Accessory for the device
           Made by that Mfg
           Of this Device Type
           With this Product ID.

This may not be perfect, because I do not know all the details of how your devices are categorized, so you might be able to just use the Product ID. But this is a guideline to help get you on the right track.

[UltimateNeo24]

I am tring to do what you say here, but am still a little confused on how to get the manufacturer, product, model and accessories stored in the table multiple times with an ID. then get the name back out on a report. As when i o the queries for the accessories it says too many oter joins. Or same field name as the accessory Name would be used multiple times if more than 1 accessory bought in by the customer. I think iam doing the tables ok for the manufaturer product and model as if i not relate them i cant use my query to filter the combobox selections. Accessories are bound to the product only. Hopefully the images help to show what i have done it is just a case of linking it together so i can get the name sback out for a job card/sheet thta shows the customer what they broght in and what was wrong with it. As there is 3 stages booking in Quoting (Parts), Completed job.

This is just the booking in stage.

I am ok with cascading comboboxes, but as they save the ID value in the corresponding table i am then trying to get that data out for use in a printable sheet of paper for the customer. I can query manufacturer, product and model if only 1 item bought in by the customer.

[imgnothumb]https://bytes.com/attachments/attachment/9821d1545098643/accessories.jpg[/imgnothumb]
[imgnothumb]https://bytes.com/attachments/attachment/9822d1545098643/models.jpg[/imgnothumb]
[imgnothumb]https://bytes.com/attachments/attachment/9823d1545098643/relationships.j pg[/imgnothumb]

[twinnyfo]

I will get back to this in a brief bit. I think you are getting closer, but I also think there are some fundamentals that you are not quite grasping.

If you are getting an error that says there are too many OUTER JOINS, then you must be doing something terribly wrong in building your query.

I will re-create your sample tables and then demonstrate. Be right back.

[UltimateNeo24]

ok thank you for your help

[twinnyfo]

First, take a look at the relationships here:
[imgnothumb]https://bytes.com/attachment.php? attachmentid=98 24&stc=1&d=1545 136437[/imgnothumb]
As you can see, this is very similar to what you had. We are only saving the IDs of the various tables as Foreign Keys.

Now, the heart of your question has to do with, "How do I display a Product Name, when all I have is the ProductID?" This is really at the heart of what Relational Databases are all about. Conceptually speaking, if you have the ID from any table, then you already have everything else about that Product. How? Just refer to those items via the relationship that is already built.

Here is an example, using a Query:
[imgnothumb]https://bytes.com/attachment.php? attachmentid=98 25&stc=1&d=1545 136437[/imgnothumb]
The SQL for that Query looks like this:

SELECT     tblInvoices.InvoiceNumber, 
           tblInvoices.InvoiceDate, 
           tblCustomers.CompanyName, 
           tblProducts.ProductName, 
           tblProducts.ModelNumber
FROM       tblProducts 
INNER JOIN ((tblCustomers 
INNER JOIN tblInvoices 
ON         tblCustomers.CustomerID = tblInvoices.CustomerID) 
INNER JOIN tblInvoiceItems 
ON         tblInvoices.InvoiceID = tblInvoiceItems.InvoiceID) 
ON         tblProducts.ProductID = tblInvoiceItems.ProductID;

As you can see, we don't even list ANY of the IDs in our Query as output fields. What we do use is the various items within those tables referred to using the various IDs and the relationships we've established for those tables.

I hope this makes sense, because this is one of the very basic, foundational principles concerning Relational Databases.

Let me know if you have additional questions. I'd be glad to keep working through this with you.

[PhilOfWalton]

@Twinnyfo

I have been watching this thread and due to health problems not commented.

Looking at Post #11, certain things concern me.
1) Are the IDs Autonumers? There is an implication on the field names in Models.JPG. that they are not. Also are the various Company Name, Accessory name, manufacturer name etc. set a indexed (No Duplicates)

I think that OP was correct in his relationship diagram that you need a table of Models as well as a table of products with the ProductID and ManufacturerID as the FKs in the TblModels. The way you set it out, the ProductName would be repeated many times as in the Model.Jpg

That would of course mean that TblInvoiceItems would use ModelID rather than ProductID.

The OP may also want to add a TblServiceTecs.

Sorry to interfere and tell me what to do if you think I am wrong.

Phil