a General Form structure/work question (LONG)

[laythss]

hello all, I am very new to access (my background is in VBA Excel, but I am not using VBA for access yet).
I was wondering if some of the more advanced people can give me pointers on how to accomplish (or work my way) to make the kind of form I am trying to do.
What I am trying to do is the following:
I have a vending company with couple of products such as espresso, latte, black coffee, etc.. (all of the products are sold at the same price for each customer, so I might sell the products for company A for .75, but for company B for .55 etc) and I am selling these products to many clients company A, B, C, D, etc. As A start I created a table with my products, then I created a table for my clients (this one includes name, address, contact person, and machine number ).
I would like to make a readout input form where it would get the customer name, machine number, each of the product's total for the readout period (this is usually every week), and the date of the readout. Now I would like the form to add a record in a table for the client machine number that was selected and maybe also in another table for the overall machine ( I am not sure how queries work, and weather I would need that or I can use queries for it).
My ultimate goal is to be able to do the following with the data:
- check (and perhaps graph) the overall statistics for a each of the products for all clients
- do the same for a certain client
- do the same for a certain machine at a client
- check the overall sales of all products to all clients for each month of the year
- do the same for a particular client or for an individual machine
- do the same but for each year

I have done all of this in a VBA Excel based program but I was hoping to change the program to be access based instead of excel based, since this would make the data more centralized and easier to maintain, backup, check, and update.

So if anyone can post any hints or suggestion about how I should build my tables, the usability of quarries, and especially the construction of the forms I would very much thank you for that, since these are my first days getting to know access and I am absolutely confused with it, all the language you are supposed to put into text boxes, combo boxes etc....

Also if anyone knows of any good links to some online tutorials would be great, since most for the tutorials I found on a search was for constructing a for or a query using the wizard, but with no real discussion of how to bound the data to certain fields etc.

Layth
I thank you all in advance.

 

[laythss]

More detail qestion

Can a Foreign Key point to ONE of the Combined Keys in a another table??
I read an article about rules of normalization etc (figured that was a good place to start) so I am running into the following problem.
I have a table (Clien_info) as follows:
ID(Auto)[PK] Client_Name Billing_zip Contact_last_name Machine_number[FK]
1 aaCorp 93101 Smith 200
2 aaCorp 93101 Smith 210
3 bbCorp 94070 Young 201
4
..
I have a table (MachineList) as follows:
Machine_Number[PK] Product_List_number [FK]
201 1
210 2
200 1
and I have another table (ProductListDetail) as follows:
Product_List_number Product_name
1 black coffee
1 latte
1 chocolate
2 hazelnut coffee
2 black coffee

this primary key for this last table can be a combo of the two columns

so can I point Product_List_Number in the MachineList table to Product_List_number in ProductListDetail table.

what I am trying to do:
each client would have one or more machines installed at their location, these machines have a variety of option of products that can be included, in the example I gave above aaCorp has two machines installed (200 and 210) the machine 200 will dispense black coffee, latte, and chocolate, yet the other machine (210) will only dispense Hazelnut Coffee and black coffee.

Is there a better way of doing it?

 

[Pat Hartman]

Machine_number[FK] doesn't belong in the client table. ClientID belongs in the MachineList table as a foreign key. You have the relationship backwards. The way you have it, each client can have only one machine when in fact he may have many.

When making 1-many relationships - the 1-side key goes into the many-side table as the foreign key.

I don't understand what the product list is but I'm sure that the relationships in the last two tables are also incorrect. I think you should have:
tblClient:
ClientID (PK)
ClientName
other client details

tblMachine:
MachineID (pk)
SerialNumber
ClientID (fk to tblClient)
other machine details

tblProduct
ProductID (PK)
ProductName

tblMachineProduct
MachineID (pk fld1, fk to tblMachine)
ProductID (pk fld2, fk to tblProduct)

 

[laythss]

Thanks You Pat, I was reading one of the Help Tutorial in access and noticed what you said, I have my relationships upside-down.
I constructed the tables in access, but I am still not sure how to make the most important part: which is the readouts.
Every machine gets readout every week, and then readout is in the format:
Product1 10 cups
produc2 20 cups
product3 30 cups
.. etc

the way I was thinking is:
tblReadout:
year_date
month_date(pk fld1)
day_date(pk fld2)
ProductID(fk to tblProduct)
MachineID(fk to tblMachine)
ReadoutAmount

Is this the best way of constructing this??
Layth

 

[Pat Hartman]

The parts of a date should not be separated.
tblReadout:
ReadoutID (autonumber pk)
ReadoutDate
MachineProductID (fk to tblMachineProduct)
ReadoutAmount

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.