Setting Up Complex Relationships

[seanbhola]

Hi everyone,
I need to know how to relate a field in an access 2007 table to multiple records in a field in another table using the example below.
I need to relate tables so that when i select week 1 for calibrating pt-3 it automatically can relate tables and create a subdatasheet to tell me either 1/1/2011 or 1/2/2011 in table 3 it was calibrated.

The example is:

Lets say i have 20 instruments in a table (table 1) and i have to calibrate them during the year.

Table 1
Instrument description

pt-1 pressure transmitter 1
pt-2 pressure transmitter 2
pt-3 pressure transmitter 3
tt-4 temperature transmitter 4
tt-5 temperature transmitter 5
lt-6 level transmitter 6
lt-7 level transmitter 7
ft-8 flow transmitter 8
ft-9 flow transmitter 9
etc

Because access 2007 has a limit of 255 for the number of fields it can create i cannot create 365 field each named every day of the year.
So what i did i made a table with 52 fields ie 52 weeks in a year (week1, week 2,etc as in table 2 below)

Table 2


Week1 Week2 Week3 Week4 etc
Yes No Yes No
No No Yes Yes
etc

and another table (table 3) with 2 fields (1 for week of the year and the other for day of the year) eg

Table 3
Week of the Year Day of the Year
1 1/1/2011
1 1/2/2011
2 1/3/2011
2 1/4/2011
2 1/5/2011
2 1/6/2011
2 1/7/2011
2 1/8/2011
2 1/9/2011
3 1/10/2011
3 1/11/2011
etc

[ajetrumpet]

that is not correct, plain and simple. that is an excel concept put in access.

why don't you tell us simply what you need to do? as in, WHAT you need to have tracked? I have no idea what calibration means, nor do I have any desire to learn, but I do know that this could probably be set up in this program with only a few fields in a few tables, if that.

so what is your situation, in a nutshell? what do you do, and what data do you need to be monitoring?

[seanbhola]

I am an instrument technician who needs to track the calibration during the year of about 5000 instruments on a plant. Think of it as having 500 cars in a car rental firm and every day that a different car is sent for a service i have to track it. Also each car has a scheduled day to be serviced.
So a table (1st) for the cars with description, 2nd table for scheduled days these cars are to be serviced, and 3rd table for when they are actually serviced either before or after the scheduled date.
The problem is i cant have 365 fields for each field (each day of the year) in the 2nd table so i had to put each field as one week that is 52 fields for 52 weeks in a year. Then i had to create a fourth table with 2 fields, week of the year and day of the year. How do i relate the fourth table with the second table?

[ajetrumpet]

So a table (1st) for the cars with description, 2nd table for scheduled days these cars are to be serviced, and 3rd table for when they are actually serviced either before or after the scheduled date.

this is incorrect. that's the point of my entire post. you are treating this program like a spreadsheet. I can't help you if you continue to do it.

and again, you didn't even answer what I asked you. WHAT do you need to track?? you got close, talking about the calibrations. but that really doesn't mean anything. for instance, HOW? changes in calibration? actuals vs. historical figure data? variances in predications, etc... at the end of the day, in many companies, it really doesn't matter how you do something as long as you get to the end of the road where your target is at right?? if the boss wants a report of a particular nature, your data needs to be set up to accomodate that, otherwise you can wast your entire day messing about with this program.

an example for ya - if you have to, and the end of the current month, send a report to your boss showing:

• every car
• the date of scheduled service
• the date that each was ACTUALLY serviced

then you need AT MOST, two tables:

1) CARS and their details
2) information about services done to the cars (child table)

understand?? I used cars here, but obviously it's your instruments that need attention in your scenario.