Complex Query

[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, they want 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

 

[RJN]

When you say 'line' are you talking the output from these queries are placed onto a new line on a web page or a form perhaps? I don't know if I see what your question is really. Are you asking how to place a new line or asking how to write the 2nd query? I would assume line but I dont know what kind of environment the data is being sent to (web or access). From my interpretation, these queries don't depend on one another so just write to queries, have to result sets, and place them where each needs to be.

 

[semaj21]

Complex query

Hello RJN
Thanks for taking an interest. I have created a Union query which does the job of combining the two tables and keeps the data on different lines, the problem I now have is when I try to link the Union query with the two other tables I get a mismatch error from access, is this because the Union query is just a snapshot of the data and all the Formatting is lost? I am new to union querys and would be greatfull of any help. Below is a sample data sheet of how the final query must look. As you can see the MODID data is not on the same line as the TOPID data. These are two seperate tables which are linked to two more tables. I need to design a query which will show all the data but the TOPID and MODID must be on a seperate line as shown on the attached file.
Regards Barry