Complex Querie

[semaj21]

I have three tables called, Clients basic details, Episode and Modality. There are some 56 fields in these tables. Once a month I run a query on these tables. The data is then uploaded to another site via the internet.
I have now been asked to collect data into another table called TOPS which has some 23 new fields in it.
The problem I have is, none of the TOPS data to be on the same line as the Modality Data. For instance, there are 79 fields in the query which must conform to certain parameters before the data can successfully uploaded, the query must show lines of data for all the 79 fields, but if any line has TOPS information the line cannot have Modality information in it and vice versa. I think some of the data that would be collected will be duplicate, i.e. from the episode and clients basic details tables.
The query must show all 79 field headings. Any line of Data in the query result that has data from the Modality table and data from the TOPS table can only show the returned data from one of these tables, the values from the other table must be left blank and vice versa.
For Instance say From the clients details table we show the clients name, from the episode table we show the number of children he has, from the Modality table we show he has structured intervention, we then must show blank records from the TOPS table.
Then on another line the query must pick up the data from the TOPS table, episode table and clients basic details table and leave the Modality table fields blank.
A lot of the info from the Clients and episode table will be duplicated but on the different lines.

Any Help would be greatly appreciated
Barry

 

[PHV]

I've understood nothing about your issue but i guess you want to use an UNION query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[semaj21]

Sorry its so confusing Below is the type of data sheet I need i.e. it shows different table data on a new line. This was made by, as you said a UNION Query. My problem is I need to Link this Union query to two other tables in my Database. When I try to Access returns a message saying Data mismatch. Is this because the Union query is just a snapshot and all the Formatting is lost?
I am new to Union queries and would appreciate any guidance.
ID Date Type TOP.ID ALC DRG
1 01 Prs
1 Y N

 

[lespaul]

A union query is used to join the same information from different tables into a single result set. The union query is most often used with a history table and a current table where the field names correspond to each other. The two tables remain in separate locations but the Datasheet View of the union query shows the records from one table below the other.

Is there some "key" field that relates the Clients basic details to the Episode and Modality tables? If so, you need to "JOIN" the tables together based on the "key" field. (read the Understanding SQL Joins link below for more information. This will give you a single record with all the data from all the tables.



Leslie

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

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins