design/structure question

[laythss]

Hello all, I am starting to get an idea of how access works (at least I know now where the surface is). I am having a design/table/form problem. Here is what I have:
a form called 'Enter Product' , in this form I have a YearDate, YearMonth,YearDay fields (I am not sure how queries work at this point of time, and if searching through dates is reliable, so these filelds might just be for the moment and replaced later on with a date field) these three text boxes are bound to the Readouttbl.
I have a combo field MachineID also Bound to the readouttbl and gets it's values from the MachinListClients_table.
I have a combo field ProductID also Bound to the readout table and gets it'd values from the ProductList_table
and a field readoutAmount bound to the Readouttbl

what I want to do: is when the user opens this form they are able to add the amount of cups (readoutAmount) for each Product a machine has at that readout date

Now the only way I see that possible is to add many extra colums in the Readouttbl (product1 , readout1, product2, readout2, product3, readout3, ....)
Is this the right way to structure this? or is there a better way?
I am attaching my database file so it is easier to understand the structure that I have (which might be absolutly wrong, please comment if you see anything that you think should be changed in any part of the database)

 

[jeremie_ingram]

I have read your post and looked at you example, but cant quite grasp what it is your attemping to do. What is the overall goal here, what data are you wanting to track.
I am guessing that you have various clients, which are assigned either one or many machines. Each machine contains various products. You want to track the usage of the products for each machine on given dates.
Is this correct?

 

[Pat Hartman]

I told you in this post - http://www.access-programmers.co.uk/forums/showthread.php?t=92600 - what your readout table should look like. Did I waste my time?

 

[laythss]

I am sorry Pat. I just deleted a whole paragraph explaining why I was looking for something else on this post, and then after reading you threads again on the last posts .. They seem to be the same. My mind is just getting twisted with these tables, and I am still not getting it all figured out.
Here is where I got confused:

'
make a unique index on the combination of ReadoutDate and MachineProductID to enforce the business rule that only one readout of MachineProductID may occur for any date.

Change this table to facilitate the above table:
tblMachineProduct:
MachineProductID (autonumber pk)
MachineID (fk to tblMachine)
ProductID (fk to tblProduct)
Instead of using the MachineID and ProductID as the primary key, make a unique index containing the two columns to enforce the business rule that there should be 1 and only 1 instance of the combination.

This is where I am just plain lost. You are saying that MachineProductID contains the two colums of MachineID and ProductID but we have it set as (AutoNumber) so how does it contain the both of them?

 

[laythss]

I am including an updated file,
Including my last question in the past post about indexes, how would the form be constructed to be user friendly ---> how would the user figure out the 'MachineProductID' ?
Check the 'Enter Readout' Form in the attached file to see what I mean.

 

[Pat Hartman]

Without building the application from scratch, I can't really work with your db. You need to do some reading on normalization so you have a better idea of how tables work. For example, your readout form is in single form view but it should be in continuous or datasheet view. You have duplicated the fields so that you have 7 sets of controls but they are all bound to the same single set of fields. Remove the extra six sets and reduce the size of the form. Go back to my original suggestion for the machine/product table and remove the autonumber. Make a two field primary key. Your form now needs two columns for machine and product rather than just one. You will need to use the cascading combos concept so that when you pick a machine in the first combo, the second combo shows only the products that that machine vends.