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 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!

[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_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

[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

[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 (tblInvoiceProducts). That Table would have a Foreign Key to ProductID. Then, one more Table that lists all the Accessories for each Product ordered (tblInvProdAccessories). 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:

tblAccessories
 
 Field      Type
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.

[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:

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:

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

[twinnyfo]

Phil,

Your comments are always welcome.

Oddly enough, I built a Tbale (tblProductTypes) which would help things along, too, but forgot to incorporate it into the relationshiips. This is what I get for just throwing something together.

Yes--OP would need a tblServiceTechs. I was not trying to be exhaustive, but demonstrative.

PhilOfWalton:
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

I think it just depends on what is considered a "product". Is a Canon EOS Rebel Ti the same "product" as a Canon EOS 5D? Yes, they are both cameras, but I would place them user the same product type, but different products. It also depends upon "to which nth degree" do we split out separate characteristics in separate tables. there are time when we should and there are times that we simply bring different characteristics under a more general umbrella.

Believe me, I take your comments to heart. There may be good reasons to split things out as you say. I have always respected your judgment and experience in such things.

[PhilOfWalton]

Kind of you to say such nice things.

I was simply looking at the data in Models.JPG where there are loads of duplicate product name.

As for cameras,in my extreme ignorance, I would classify the product as SLR cameras.

Phil

[twinnyfo]

Agreed that OP will probably have ongoing normalization issues.

OR>>>>

5D: Electronics>Cameras>Digital Cameras>Digital SLRs>Mid Level
EOS Ti: Electronics>Cameras>Digital Cameras>Digital SLRs>Entry Level

A demonstration of normalization ad nauseum....

:-D

[PhilOfWalton]

I think you are taking it too far. All the OP is interested in is Model Number & Product.

I don't know what your guidelines are, but if I see data repeated more than about 5 times in a table, I seriously consider creating an additional table.

So instead of typing (or mis-typing) the product name, it comes from a drop down, and should eliminate errors, Especially important when searching for a model begining with ESAM from the Auomatic Coffee machine (or is it Automatic Coffee machine?)

Phil

[twinnyfo]

I think we are agreeing with each other....

The Machine Type (however it is named) ought to be a separate table. And the Accessories listed for each of those machine types should also have a separate table. This would also allow adding mew products on the fly, as well as new accessories to each type of product.

[PhilOfWalton]

Yes, but OP's post #9,second paragraph, needs clarification. At one point he says that any unique product from a particular manufacturer supports a unique set of Accessories. If that is correct, then your relationship is spot on using TblProdAccessories.

As an aside, it is going to be "interesting", if a customer orders several different models of the same product together with some accessories, to total the identical accessories.

Phil

[UltimateNeo24]

I think we are getting there, however when i have created my comboboxes i can only select 1 model it wont change. i can see the list of all the models. But it only selects the 1st one in the list.

I havent put any customer data in at the moment just tried out the comboboxes. The form i know does only give option of 1 product at a time. I have seen the datasheet sub form that would allow multilple products, then the accessories would have to be for each product. Might be too much to put on 1 form.

Plus if it was a returning customer we would want to pre fill the customer data prob show the previous product bought in and be able to select it if the same and then just re do the accessories they bought in as they might bring in less accessories for example. It might be a return customer the repair didnt work so they bought the unit back in within the 3 month period. It might be the replaced part broke again or a different fault, which you might need to charge for.

All the IDs are autonumbers customerID might be good for a unique job no starting at 100000. I am looking at an autonumber that is replication ID as there is a limit in access for autonumber although a large number. Replication ID is unlimited. The CustomerID being 100000 would be a way to search for that customer, otherwise a phonenumber would also be unique but would ave to search 3 fields as Business, Home and Mobile options are given. Usually ID numbers are not visible to the user, but it is what we had before was a job number e.g. 99678
and they like to keep it. Searching by name is also possible but that would show records of all customers with same name. If the customer is only in the customers table once it should show all visits (Not sure if this should come up when entering the customers details) Prob best to search for the customer and display all the visits. Although having that info at booking in is helpful, it might be showing too much data all at once to be clear enough for users.

What are your thoughts?


example of code i am using

Private Sub cboproduct_AfterUpdate()
'Me.Quote_Fee = Me.cboproduct.Column(3)
With Me.cbomodel
 
ModelIDSet Nz(Me.cbomanufacturer.Value), Nz(Me.cboproduct.Value)
.Value = ""
.Enabled = True
End With
'With Me.modelimage
'.Picture = ""
 
'End With
'With Forms![Booking In]![NavigationSubform].Form![Sub Frm Add Customer Accessories].Form!cboaccessory1
'Accessory1IDSet Nz(Me.cboproduct.Value)
'End With
End Sub
 
Private Sub ModelIDSet(lngManufacturerID As Long, lngProductID As Long)
On Error GoTo ERROR_HANDLER
    Dim QD As DAO.QueryDef
        Set QD = CurrentDb.QueryDefs("QryModel")
        QD.Parameters("@ManufacturerID").Value = lngManufacturerID
        QD.Parameters("@ProductID").Value = lngProductID
    Set Me.cbomodel.Recordset = QD.OpenRecordset
EXIT_ERROR_HANDLER:
    If Not QD Is Nothing Then
        QD.Close
        Set QD = Nothing
    End If
Exit Sub
 
ERROR_HANDLER:
MsgBox Err.Number & ": " & Err.Description
Resume ERROR_HANDLER
Resume
End Sub

[twinnyfo]

UltimateNeo24:
I think we are getting there, however when i have created my comboboxes i can only select 1 model it wont change. i can see the list of all the models. But it only selects the 1st one in the list.

I think you'll have to explain this one a little better. I think I understand what you are saying, but I am a bit confused as to what is going on. The initial thought (oddly enough it happened to me yesterday) is that your combo box is bound to the underlying query and that query is non-updateable. This will prevent you from making a different selection.

[UltimateNeo24]

Thanks, twinnyfo i got it sorted now. I will try the accessories next

[UltimateNeo24]

Hello I have tried to do a multiple item form. But when a customer brings in several items at once or say is a repeat customer the data from the form disappears.

[twinnyfo]

UlitimateNeo24:
Hello I have tried to do a multiple item form. But when a customer brings in several items at once or say is a repeat customer the data from the form disappears.

I guess I “understand” what you are saying but this makes no sense and needs further description.

In theory, what you should have is a form for Invoices. You click a command button to add a new invoice. That invoice has all necessary information for just that one invoice. If the customer is a new customer, then you should be able to add a new customer, then select them as the customer for that invoice. If they are an existing customer, you should be able to select them. Yes, even their contact information should pop up so you can validate it with them while they are there.

That Invoice form should have a sub form on it for products. This will probably be a continuous form that you can select the product the customer is bringing in for repairs. Because Access does not allow continuous sub forms to have embedded continuous sub forms, you will probably have to engineer a pop-up form that lists the accessories that you can select from each product. This information can be easily represented on a report, but this can only happen through some other way with your forms.

Please keep in mind that this thread has already gone considerably beyond the bounds of the original question (which has really been answered in Post #14). I highly recommend you begin another thread (or additional threads).

Also, I do get the impression that, rather than developing a sound database, you are more apparently flailing aimlessly with a complex project, not having understood the more basic concepts of relational databases and then responding (or reacting) to recommendations that you may not quite fully understand. Please don’t take this as a slight. For those of us (like me) who have been self-taught MS Access, this is a stage we have all gone through. It may be wiser for you to build something gradually. We may all want to end up with a Mercedes Benz, but if we don’t know how to make an internal combustion engine, or even form steel into an appropriate frame, our efforts are for naught.

Additionally, Bytes is not typically a forum for “instruction”—although I have tried to help some users work projects from scratch. That is typically not how things work here. We are glad to troubleshoot specific problems, but few (if any of us) have time to devote to working through someone else’s project. And, realistically, trying to communicate via forum is not helpful for that type of work.

That being said, we are glad to continue helping you with specific troubleshooting tips. But your questions should be more limited in scope.

Thanks!