Linking 3 tables with common data

[swlaw]

I have three tables Project, O&M and Submittals. The common item is Project
Number. I want to link the three tables to the same project number.
I am having a hard time getting this to work.
I am fairly new to Access 2007.
Can someone please help.

 

[Jerry Whittle]

Open up a new query in design view. Select the three tables that you mentioned.

Next drag and drop the Project Number field from the Project table onto the
Project Number field in the O&M table.

Do the same thing from the Project table to the Submittals table.

Select the fields that you wish to see from each table and drop them in the
columns below.

Run the query. There's a good chance that you may not see some Projects. If
there is a Project Number in the Project table but no matching Project Number
in the Submittals table, it won't be returned. To fix this go back to query
design view. Double click the line between the two tables until a dialog box
shows. Change it from Option 1 to Option 2 and see if that works better. You
may need to do the same thing with the other table.

 

[John W. Vinson]

I have three tables Project, O&M and Submittals. The common item is Project
Number. I want to link the three tables to the same project number.
I am having a hard time getting this to work.
I am fairly new to Access 2007.
Can someone please help.

What have you tried? In what way did it "not work"? What's the context: are
you trying to create a query, a Form, a report, or what?

For data entry I'd expect you could use a Form based on Project with a Subform
for each related table, using Project Number (which I'd rename to
ProjectNumber, blanks in fieldnames can be a hassle) as the subforms' Master
and Child Link Fields.

Once you have data in the tables, a Query joining the tables appropriately
should work fine, say for reports. If the records in O&M (which I'd also
rename, the & character is a special character for defining hotkeys) are
unrelated to the records in Submittals, you may want instead to use a Report
with Subrebports.

 

[swlaw]

Thanks Jerry,
I did what you suggested and I received the following error when I tried to
run the Query. "Type mismatch in expression"

Do you know what this means?
Thanks for your help.
S

 

[Jerry Whittle]

Show us the SQL for that query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

 

[swlaw]

SELECT Project.[Project Number], [O & M].[O&M Approved Date],
Submittal.[Submittal Date]
FROM (Project INNER JOIN [O & M] ON Project.[Project Number] = [O &
M].[Project Number]) INNER JOIN Submittal ON Project.[Project Number] =
Submittal.[Project Number];

 

[Jerry Whittle]

Nothing obvious from the sql statement.

Open up each table in design view and make sure that the Project Number
field in all three tables are the same data type. If one is Text and the
others a Number, that might explain it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT Project.[Project Number], [O & M].[O&M Approved Date],
Submittal.[Submittal Date]
FROM (Project INNER JOIN [O & M] ON Project.[Project Number] = [O &
M].[Project Number]) INNER JOIN Submittal ON Project.[Project Number] =
Submittal.[Project Number];

Show us the SQL for that query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

 

[swlaw]

Jerry,
I did change the data type to all the same, text. However, when I ran the
query I could only see some of the data. And when I changed the settings the
data would move around and not show other data.
Do you think I can send you my test database and you have a look at it?
Steve

Nothing obvious from the sql statement.

Open up each table in design view and make sure that the Project Number
field in all three tables are the same data type. If one is Text and the
others a Number, that might explain it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT Project.[Project Number], [O & M].[O&M Approved Date],
Submittal.[Submittal Date]
FROM (Project INNER JOIN [O & M] ON Project.[Project Number] = [O &
M].[Project Number]) INNER JOIN Submittal ON Project.[Project Number] =
Submittal.[Project Number];

Show us the SQL for that query. Open the query in design view. Next go to
View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks Jerry,
I did what you suggested and I received the following error when I tried to
run the Query. "Type mismatch in expression"

Do you know what this means?
Thanks for your help.
S


:

Open up a new query in design view. Select the three tables that you mentioned.

Next drag and drop the Project Number field from the Project table onto the
Project Number field in the O&M table.

Do the same thing from the Project table to the Submittals table.

Select the fields that you wish to see from each table and drop them in the
columns below.

Run the query. There's a good chance that you may not see some Projects. If
there is a Project Number in the Project table but no matching Project Number
in the Submittals table, it won't be returned. To fix this go back to query
design view. Double click the line between the two tables until a dialog box
shows. Change it from Option 1 to Option 2 and see if that works better. You
may need to do the same thing with the other table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have three tables Project, O&M and Submittals. The common item is Project
Number. I want to link the three tables to the same project number.
I am having a hard time getting this to work.
I am fairly new to Access 2007.
Can someone please help.

 

[Jerry Whittle]

Sure. Send it to (e-mail address removed)

I'm teaching tonight so it might be a couple of days before I can look at it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
I did change the data type to all the same, text. However, when I ran the
query I could only see some of the data. And when I changed the settings the
data would move around and not show other data.
Do you think I can send you my test database and you have a look at it?
Steve

Nothing obvious from the sql statement.

Open up each table in design view and make sure that the Project Number
field in all three tables are the same data type. If one is Text and the
others a Number, that might explain it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT Project.[Project Number], [O & M].[O&M Approved Date],
Submittal.[Submittal Date]
FROM (Project INNER JOIN [O & M] ON Project.[Project Number] = [O &
M].[Project Number]) INNER JOIN Submittal ON Project.[Project Number] =
Submittal.[Project Number];




:

Show us the SQL for that query. Open the query in design view. Next go to
View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks Jerry,
I did what you suggested and I received the following error when I tried to
run the Query. "Type mismatch in expression"

Do you know what this means?
Thanks for your help.
S


:

Open up a new query in design view. Select the three tables that you mentioned.

Next drag and drop the Project Number field from the Project table onto the
Project Number field in the O&M table.

Do the same thing from the Project table to the Submittals table.

Select the fields that you wish to see from each table and drop them in the
columns below.

Run the query. There's a good chance that you may not see some Projects. If
there is a Project Number in the Project table but no matching Project Number
in the Submittals table, it won't be returned. To fix this go back to query
design view. Double click the line between the two tables until a dialog box
shows. Change it from Option 1 to Option 2 and see if that works better. You
may need to do the same thing with the other table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have three tables Project, O&M and Submittals. The common item is Project
Number. I want to link the three tables to the same project number.
I am having a hard time getting this to work.
I am fairly new to Access 2007.
Can someone please help.

 

[swlaw]

Jerry, I have figured it out.
John, what I was doing wrong was I did not identify the foreign key in the
next table which tied to the primary key in the preceeding table.
So now I have 10 tables in relation to one another.
S

 

[swlaw]

Jerry,
I was able to figure out what I did wrong. See other note to John.
Thanks for your help.

I would like to move on to my next task. I have a form called New
Project, all new projects will be entered into this form. What I would like
to do is when I enter a new project, I would like to be able to click a
button and it take me to another form (Start Up) and the same project number
that I just entered into New Project, I would like to have appear in the
Start Up form in the Project Number box.
The botton function is not the issue, it is having that new project number
appear in the forms, there will be four forms that I will need to enter data
that corresponds to the new project number.

Any suggestions.... S

Sure. Send it to (e-mail address removed)

I'm teaching tonight so it might be a couple of days before I can look at it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
I did change the data type to all the same, text. However, when I ran the
query I could only see some of the data. And when I changed the settings the
data would move around and not show other data.
Do you think I can send you my test database and you have a look at it?
Steve

Nothing obvious from the sql statement.

Open up each table in design view and make sure that the Project Number
field in all three tables are the same data type. If one is Text and the
others a Number, that might explain it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

SELECT Project.[Project Number], [O & M].[O&M Approved Date],
Submittal.[Submittal Date]
FROM (Project INNER JOIN [O & M] ON Project.[Project Number] = [O &
M].[Project Number]) INNER JOIN Submittal ON Project.[Project Number] =
Submittal.[Project Number];




:

Show us the SQL for that query. Open the query in design view. Next go to
View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Thanks Jerry,
I did what you suggested and I received the following error when I tried to
run the Query. "Type mismatch in expression"

Do you know what this means?
Thanks for your help.
S


:

Open up a new query in design view. Select the three tables that you mentioned.

Next drag and drop the Project Number field from the Project table onto the
Project Number field in the O&M table.

Do the same thing from the Project table to the Submittals table.

Select the fields that you wish to see from each table and drop them in the
columns below.

Run the query. There's a good chance that you may not see some Projects. If
there is a Project Number in the Project table but no matching Project Number
in the Submittals table, it won't be returned. To fix this go back to query
design view. Double click the line between the two tables until a dialog box
shows. Change it from Option 1 to Option 2 and see if that works better. You
may need to do the same thing with the other table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have three tables Project, O&M and Submittals. The common item is Project
Number. I want to link the three tables to the same project number.
I am having a hard time getting this to work.
I am fairly new to Access 2007.
Can someone please help.