Query

[tikkat3]

I could use some advice on how best to structure a query. I have the following fields:

Contract, Activity Date, Product1, Quantity1, Product2, Quantity2, Product3, Quantity3, Product4, Quantity4, Product5, Quantity5

At the data entry stage Products1-5 are entered from a combo box, which takes its data from a Products table. In otehr words, the drop down menus for all 5 fields are identical.

The quantity fields refer to the number of products used in the corresponding Product field.

My query needs to deal with month end sums. What I want to do is create the sum of Quantity fields 1-5 which equals product code "P*" in the 5 Products fields. The same formula needs to be created for the other 8 product groups so that it can be out put into a report.

I cannot find a way of making this work for me. Does anyone have any thoughts?

 

[namliam]

You need to redesign your tables/db (sorry to be harsch)

But having this design is a major pain... instead of having multi products and quantities in one table... you need to have a related table that contains only Product/Quantity and then relate that back to your contract.

In your current design what happens if you want 6 products, then 7?? You have to add columns... BAD BAD REAL BAD.
In my suggested design you can add as many products as you like, also your question for the query wouldnt excist becuase it would be simple to do.

IF you are going to persist in this faulty design, a UNION query will save the day for you, but it is not prefered. It is merely a temporary patch on a sloppy design (again sorry to be harsch).

 

[tikkat3]

Thanks for responding Mailman. I sort of understand what you mean, but not fully. If I have a table with just Product and Quantity (along with Contract to link the record back to the main table), when it comes to data entry through a form, how am I able to enter and view more than one product?

In most cases there will be at least two products per contract, but never more than five (which is why I had set up my table as mentioned earlier).

The products are entered through a sub form on the main form.

Since I first wrote about the query problem, I have had a major difficulty with the whole database. The relationships are not working as they should (and as they did). The Contract field in my main table relates to three other Contract fields (FK) in other tables, so linking the data together. Although they are showing on the relationship table correctly, there is only a link to one of the other tables, and not to the others. It does not make sense, especially as it was all working fine just 3 hours ago!

 

[namliam]

Thanks for responding Mailman. I sort of understand what you mean, but not fully. If I have a table with just Product and Quantity (along with Contract to link the record back to the main table), when it comes to data entry through a form, how am I able to enter and view more than one product?

Use a subform...

In most cases there will be at least two products per contract, but never more than five (which is why I had set up my table as mentioned earlier).

FOR NOW, in future that will undoubtably change. Despite this fact the design is still not prefered.

The products are entered through a sub form on the main form.

In which case use a sub-sub form?

Since I first wrote about the query problem, I have had a major difficulty with the whole database. The relationships are not working as they should (and as they did). The Contract field in my main table relates to three other Contract fields (FK) in other tables, so linking the data together. Although they are showing on the relationship table correctly, there is only a link to one of the other tables, and not to the others. It does not make sense, especially as it was all working fine just 3 hours ago!

Cannot help you on that one... Have you tried the "default windows" solution?? Shut down Access, now open it, see if it works.
If not, reboot your machine and see if it works.
Now it still doesnt work, go searching for another reason.

 

[tikkat3]

Thanks for your help on this yesterday. The subform in datasheet view was the solution and makes life so much simpler. Your suggestions all worked perfectly.

My problem with the database itself was eventually tracked down to an incomplete piece of VBA which I thought had been discarded, but was still sitting in the background messing the whole database up!