Help with database table relationships

[semaj28]

I need to create a MS Access database that includes a form. I want to put a drop down menu on this form that has 7 different names ( i.e. Machine 1, Machine 2, Machine 3, etc.) Each of these selection needs to have its own table that the user can access. For example, if the user selects machine 1, then they would receive all of the data for that machine. Each of the tables would contain the exact same fields, they would just contain data specific for each machine in the drop down menu. I am unsure as to how to set this up exactly. I would also like to be able to print data from each drop down option. If your are unsure of what I am talking about and have a little free time, please let me know and I can go into more details, what I have stated here is the major portion the I am having trouble with. Thank you for your time, it is greatly appreciated.

Chris Nally

 

[sko]

First of all, why do you think you need a separate table for each machine?

 

[semaj28]

Each machine would contain data for that specific machine I need to separate the data for each machine. Different jobs would be scheduled for each machine, that is why I am assumming that I would need different tables for each machine. I am fairly new to Access and database design so I am not sure exactly sure how a database type that I am looking for wuld work.

 

[AlexMidd]

As sko implies, you do not need a separate table for each machine. Set up one table for the machines and one for the data (assuming that there could be more than one row in the data table for each machine in the machines table.

You need a primary key in the machines table, e.g. MachineID, which must uniquely identify each machine. You can then have fields in that table where there is a one-to-one relationship, i.e. only ONE row for each machine, such as MachineName, MachineDescription, MachineManufacturer, MachineLocation etc.

Then ste up your second table, called say DATA. It should also have a primary key, e.g. DataID, but also another field, called a foreign key, which has the MachineID of the appropriate machine in the Machines table. Add all the other fields that you need for each machine's data.

Go to Tools|Relationships, add the two tables to the design view, then drag the MachineID field in the Machines table to the field in the DATA table that you have allocated as the foreign key. You will see a dialog that allows you to set this relationship up as a one-to-many relationship. This means that each machine can have several items of data.

You can now set up a query that uses the machine selected in the drop-down to filter the items that belong only to that machine. Basically, start a new query with the fields that you need and under the foreign key field put in criteria such as [Forms]![Form1]![cboMachines] where Form1 is your form and cboMachines is the combo box.

There is a lot more to this than just the above but this is the basics. If you need any more info, give us a shout. Have fun! )

Alex Middleton