Query of two tables

[jwhal]

Hi there. I'm trying to create a query (or two) that I can use to produce a spreadsheet but I'm having a bit of trouble. I'm using Access 2000. I've attached a pdf showing the relationships, that way you can see the whole db.

The fields I need for the spreadsheet are to be extracted from tables "Intrusive Manhole Survey" and "Pipe_information". The required fields are:
PipeID, Firm, Inspector, Sewer System, Source, Upstream Manhole ID, Downstream Manhole ID, pipe material, Pipe shape, Upstream Invert, Downstream Invert.

The upstream and downstream inverts are to come from "Depth of Pipe"....meaning the depth of pipe where pipe_information.upstreammanholeid=intrusive manhole survey.manholeid will be the upstream invert, and the depth of pipe where pipe_information.downstreammanholeid=intrusive manhole survey.manholeid will be the downstream invert. (see table below for example data)

Some pipes have two entries (because one pipe connects to two manholes, thus two entries).
Some pipes only have one entry (I'm working with four db's, so some pipes are spread across different db's, but I'm not worried about that yet).

In my pipe_information table, I have entries like this:

PipeInfoEntryID, IntrusiveEntryID, Pipe ID, Upstream, Downstream, Pipe Material, Pipe Shape, Pipe Diameter (mm), DepthofPipe
10 4 7493 878147 200002 RCP Circular 1350 23.16
9 3 21003 200004 200005 RCP Circular 750 22.37
6 2 21004 200003 200004 RCP Circular 750 22.81
8 3 21004 200003 200004 RCP Circular 750 22.37
7 2 21005 200002 200003 RCP Circular 750 22.81
12 4 21005 200002 200003 RCP Circular 750 23.35
11 4 21007 200002 878148 RCP Circular 1350 23.13

For example, the entry for pipe 21004 should look like:
21004, <date>, <firm/inspector>, <sewer>, <source>, 200003, 200004, RCP, Circular, 750, 22.81, 22.37
Note that fields shown as <fdkd> means the data comes from the Intrusive Manhole Survey table.

I also need to make sure it grabs pipes that only have one entry in the table. Is this possible?

If anyone needs more info, please email me.

I've tried different techniques, but to no avail. Make-table, then update; query of two queries, etc...I have a little bit of exposure to SQL, but nothing great. I've made sure that all fields are the same (but I still get validation errors) and so on and so forth. Whew. Hope someone can help me.

Please see the attached pdf for the relationships.

 

[ssoltic]

Did you try a Union Query?

I'm guessing; create two querys, first from table pipe , second from Intrusive Manhole Survey, and join this query with Union query with Pipe_ID as key

 

[jwhal]

I see what you're saying, but I need two queries for the pipe_info table:
Some pipes have two entries. When that happens, I need to grab one entry that becomes the upstream data, the other entry becomes the downstream - then I need to put them together. Once that's done, I guess that's where the union query comes in to play. But the problem I can't seem to figure out (and I know it's something simple), is how I pull the upstream and downstream from the pipe_information table and place them together. I tried a make_table with the upstream data, then tried updating with the downstream, but it doesn't give me all the pipes, and besides - the pipes with one entry will need to be an append query....it's kind of messy to explain....any other thoughts?

Thanks,

Jon

 

[jwhal]

I figured I'd need three queries:

1) This query gives me the PipeID, Upstream Manhole ID, and the Upstream Depth of Pipe (aka Invert):

SELECT Pipe_Information.PipeID, Pipe_Information.UpstreamManholeID, Pipe_Information.DepthofPipe
FROM [Intrusive Manhole Survey] INNER JOIN Pipe_Information ON [Intrusive Manhole Survey].IntrusiveEntryID = Pipe_Information.IntrusiveEntryID
WHERE (((Pipe_Information.UpstreamManholeID)=[intrusive manhole survey].[manholeid]));

2) This query gives me the PipeID, Downstream Manhole ID, and the Downstream Depth of Pipe (aka Invert):

SELECT Pipe_Information.PipeID, Pipe_Information.DownstreamManholeID, Pipe_Information.DepthofPipe
FROM [Intrusive Manhole Survey] INNER JOIN Pipe_Information ON [Intrusive Manhole Survey].IntrusiveEntryID = Pipe_Information.IntrusiveEntryID
WHERE (((Pipe_Information.DownstreamManholeID)=[intrusive manhole survey].[manholeid]));

The pipe_information table has 7 entries, but there's really only 5 pipes. You'll see this when looking at the results of the two queries. The upstream query returns pipes 21003-21005, and 21007, while the downstream query returns 21004, 21005, and 7493. I need to create a table/query that contains one pipe per row, but making sure it grabs the pipes with only one entry in the db (this db is small - I have another db with over 250 pipes that I need to do this on).

3) Now I must create another query that contains all the other information I need from the Intrusive Manhole Survey table.

But how do I bring this all together? I'm not too savvy with SQL - although I've been looking into it! It seems logical that a Union query would do it, but how do I do that?

Thanks for the help!

Jon

 

[jwhal]

I'm reposting with (hopefully) a bit better of an explanation. I'm not sure if or what kind of joins I need to do this, or if it should be a make table followed by an append, then an update, or what. I have four of these db's to work with. This is the smallest db I have. The largest contains about 260 manholes, and over 500 pipes.

Here are my two tables:

INTRUSIVE MANHOLE SURVEY:
IntrusiveEntryID,Manhole ID,Date,Sewer System,Firm Initials,Inspector Initials,Benching Description,Manhole Type,Manhole Diameter (m),Sump Elevation (m),Source,Notes,Northing,Easting,Elevation,Description
4,200002,12/20/2004,Combined,TER,JAK,Half,Tee-Base,1.05,23.00,Plans,Notes go here,7455860.57,2629764.088,25.378,20035 MH
2,200003,12/20/2004,Combined,TER,JAK,Half,Tee-Base,1.2,22.81,Plans,Notes go here,7455889.868,2629677.596,25.875,20038 MH
3,200004,12/20/2004,Combined,TER,JAK,Half,Tee-Base,1.5,22.37,Plans,Notes go here,7455916.989,2629603.426,25.797,20036 MH

PIPE_INFORMATION:
PipeInfoEntryID,IntrusiveEntryID,Pipe ID,Upstream,Downstream,Pipe Material,Pipe Shape,Pipe Diameter (mm),DepthofPipe
10,4,7493,878147,200002,RCP,Circular,1350,23.16
9,3,21003,200004,200005,RCP,Circular,750,22.37
6,2,21004,200003,200004,RCP,Circular,750,22.81
8,3,21004,200003,200004,RCP,Circular,750,22.37
7,2,21005,200002,200003,RCP,Circular,750,22.81
12,4,21005,200002,200003,RCP,Circular,750,23.35
11,4,21007,200002,878148,RCP,Circular,1350,23.13

Note:
[Intrusive manhole survey].IntrusiveEntryID is the primary key for that table, and the foreign key in table pipe_information.

So here are my two queries. qryUpstream returns all pipes where pipe_information.upstreammanholeid=intrusivemanholesurvey.manholeid. The second query returns all pipes where pipe_information.downstreammanholeid=intrusivemanholesurvey.manholeid.

qryUpstream
Pipe ID,Date,Firm Initials,Inspector Initials,Sewer System,Source,Upstream,Pipe Material,Pipe Shape,Pipe Diameter (mm),USInv
21003,12/20/2004,TER,JAK,Combined,Plans,200004,RCP,Circular,750,22.37
21004,12/20/2004,TER,JAK,Combined,Plans,200003,RCP,Circular,750,22.81
21005,12/20/2004,TER,JAK,Combined,Plans,200002,RCP,Circular,750,23.35
21007,12/20/2004,TER,JAK,Combined,Plans,200002,RCP,Circular,1350,23.13

qryDownstream
Pipe ID,Date,Firm Initials,Inspector Initials,Sewer System,Source,Downstream,Pipe Material,Pipe Shape,Pipe Diameter (mm),DSInv
7493,12/20/2004,TER,JAK,Combined,Plans,200002,RCP,Circular,1350,23.16
21004,12/20/2004,TER,JAK,Combined,Plans,200004,RCP,Circular,750,22.37
21005,12/20/2004,TER,JAK,Combined,Plans,200003,RCP,Circular,750,22.81

I now have two queries each with half the info I need, and I have to bring those two queries together, with exceptions.

I need to make a table that ends up with this:

Pipe ID,Date,Firm Initials,Inspector Initials,Sewer System,Source,Upstream,Pipe Material,Pipe Shape,Pipe Diameter (mm),USInv,Downstream,DSInv
7493,12/20/2004,TER,JAK,Combined,Plans,,RCP,Circular,1350,,200002,23.16
21003,12/20/2004,TER,JAK,Combined,Plans,200004,RCP,Circular,750,22.37,,
21004,12/20/2004,TER,JAK,Combined,Plans,200003,RCP,Circular,750,22.81,200004,22.37
21005,12/20/2004,TER,JAK,Combined,Plans,200002,RCP,Circular,750,23.35,200003,22.81
21007,12/20/2004,TER,JAK,Combined,Plans,200002,RCP,Circular,1350,23.13,,

Note:
7493 does not have upstream information - this is in another db and I can manually add this info later.
21003 and 21007 do not have downstream information. Same reason as 7493.

So would this be a Union query? Is this possible at all?

Thank you very much for any help!

Jon
jwhal at nbnet dot nb dot ca