Combine two queries, not UNION? 1

[bmc1234]

I have two tables (tableA and tableB) which I need to pull data from. They are indirectly linked through a repairID field of a third table (both are linked to repairID of tableC). In my query I would like to have fields from both tables, so that if it pulls 5 fields from each table, there would be 10 fields in the query, but 5 would be blank for each line. A UNION ALL query would combine the fields and give a 5 field output for my query. Is there any way to do this?

 

[Golom]

If they are both linked to TableC via the RepairID field then can't you join them directly on that field in the query?

I don't get why "... 5 would be blank for each line ...". Are you saying that one of the tables (or perhaps both) has blank fields?

Can you provide some information about what result you are expecting to see? Preferably with examples.

 

[bmc1234]

5 lines would be blank for each record in the query because each record represents a line from either tableA or tableB, so if record 1 represents a record from tableA then the 5 fields in the query from tableB would be blank.

my tableC, known as repairdata holds relationships to a replacedparts table and a maintenance table. Both hold almost completely different information and have a different number of fields, but both relate to the repairdata table through repairID. What I will eventually do is filter the query by repairID to get only entries from replacedpart and maintenance that correspond to a specific repair.

I want to be able to build a report that will display data from both tables. Does this help? If not I'll try to give a simple example. Maybe I'm just going about it all wrong. I'm not sure if what I want is possible. Thanks for your help.

 

[rjoubert]

So tableA and tableB are not linked at all. Records from TableA don't exist in TableB, and vice versa, correct? Do these two tables have the same structure? If so, why would you have them in two separate tables?

 

[lespaul]

again, can you provide some sample data and your expected results? It's easier to "see" what you need when presented in that manner.

Thanks,

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[bmc1234]

They aren't linked directly. (By the way, by 'linked' I mean related, not linked like from different files) The tables don't have the same structure. Maintenance is pretty much a duplicate of repairdata to record changes to records in repairdata (when there is a change made to a record in repairdata, a new record is added to maintenance, this is done through VBA). replacedPart keeps track of different information related to records in repairdata. Displayed together, they can theoretically show a list of all changes and updates made to a record in repairdata, which is what I want to do.

Here's a simple example of my setup:
a database used to track repairs for customer service
A table called tblcomputers which holds information about specific computer repairs (the status of the repair, when it was received, ect)
A table called tblmaintenance that tracks any changes to tblcomputers such as a change in status
a table called tblreplacedParts which tracks parts added and removed from the computer (removed Part Number, added Part Number)

tblmaintenance and tblreplacedparts also have a timestamp field

I want to show in a report all changes (records in tblmaintenance) and all replaced parts (records in tblreplacedparts), preferably in order by a timestamp for any computer in tblcomputers

I hope this helps. Thanks.

 

[Golom]

I suppose you could do something like

Select fld1 As A1, fld2 As A2, fld3 As A3, fld4 As A4, fld5 As A5,
       NULL As B1, NULL As B2, NULL As B3, NULL As B4, NULL As B5
From TableA

UNION ALL

Select NULL, NULL, NULL, NULL, NULL,
       fld1, fld2, fld3, fld4, fld5
From TableB

which would give you the requisite 5 NULL fields on each record.

I'm sure however, that I'm not understanding what you are trying to achieve. Can you show w few records from Tables A and B and the results that you would expect to see?

 

[traingamer]

So for each entry in tblcomputers, you have 0 to many entries in tblmaintenance and 0 to many entries in tblreplacedparts?

I would go with separate queries for each table as the source for two subreports which would be tied by tblcomputers id.

You could come up with a well crafted query to display this data, but why bother?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[bmc1234]

[tt]A basic example would be this:

maintenance table:
repairID timestamp Status Rcvd Date Shpd Date
12 11/12/05 Repair 11/11/05 [blank]
12 11/14/05 Complete [blank] 11/14/05

ReplacedParts table:
repairID timestamp removedPN installedPN
12 11/13/05 1234 5678
12 11/15/05 8897 6655

I want to generate a report that might look like this:
RepairID
timestamp Status Rcvd Date Shpd Date removedPN installedPN
12

11/12/05 repair 11/11/05
11/13/05 1234 5678
11/14/05 complete 11/14/05
11/15/05 8897 6655[/tt]

 

[Golom]

I infer from this that "Maintenance" and "ReplacedParts" never have the same dates for a given "RepairID". Is that correct? If so

Select M.RepairID, M.TimeStamp, M.Status, M.[Rcvd Date], M.[Shpd Date], 
       NULL As [RemovedPN], NULL As [InstalledPN]
From Maintenance M

UNION ALL

Select R.RepairID, R.TimeStamp, NULL, NULL, NULL, 
       R.RemovedPN, R.InstalledPN
From  ReplacedParts R

Order By 1, 2

 

[randy700]

Wouldn't it be simpler to just combine the two tables? What is the purpose of storing this data in two separate tables?



Randy