question

PhilS-6684 avatar image
0 Votes"
PhilS-6684 asked thedbguy commented

Query Multiple Tables - Sort on Multiple Table Fields

Hi all

OK, I know the data structure I am about to describe probably breaks all the rules of logic and normality, but this is a data retrieval operation only.

The situation is that I will have five queries, each possibly returning info from different tables.
Within each table there is an item number field. This corresponds to an item number ballooned on engineering drawing.
For example, the query on table 1 may return material details for items 1, 2 and 5, with the query on table 2 returning details for items 3 and 4.

I am guessing here, but do I need to query those five queries to be able to sort the results into item number order???
Also, at present the fields that contain those item numbers have labels specific to each table. So how do I query the five table queries to sort on the different item number fields and order by the result?

Hope this is all clear.

Thanks

Phil

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
1 Vote"
thedbguy answered thedbguy commented

Hi Phil. It probably won't make sense until you give it a try. If you don't alias the columns, the result of the UNION query will just use the field names of the first table you listed in the SQL statement. So, if you want the result to use a different column name, you can alias those fields. Here's an example:

 SELECT Field1 AS F1, Field2 AS F2, Field3 AS F3 FROM Table1
 UNION ALL
 SELECT FieldA, FieldB, FieldC FROM Table2
 UNION ALL
 SELECT FirstName, MiddleName, LastName FROM Table3
 ORDER BY F1, F2 F3

Hope that helps...

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks DBGuy

The illustration you have given there absolutely nails what I was trying to get my head round.

Next it will be the IIF function, but I'll try to Google my way with that one.

Phil

0 Votes 0 ·

Hi Phil. Happy to assist. Good luck with your project.

0 Votes 0 ·
thedbguy avatar image
1 Vote"
thedbguy answered PhilS-6684 commented

Hi Phil. Have you looked up UNION queries and "aliasing?"

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks DBGuy

I'll check these out. Can I alias the item column in each table to the same name and then sort on that name across all tables in the union query?

Not part of the problem, but whenever you respond to my questions, your profile pic makes me laugh!

Phil

0 Votes 0 ·
thedbguy avatar image
1 Vote"
thedbguy answered PhilS-6684 commented

Hi Phil. If you use a UNION query to combine all the data from multiple tables, you only need to alias the first table. All other tables will use the same column alias in the result.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks DBGuy

But I don't think I have explained that well enough.
I will have five tables, each with one field containing item numbers.
These fields do not have the same name at the moment.
I was thinking that I needed to alias all five item number fields with the same name and then sort on that alias name.

Would that work, or should I rename the field in the individual tables to be the same and forget aliases?
I like to have the fieldname preceded by the table name, cos it makes queries easier to follow (for me).

Or, will the sort work if I simply Order By the item number field in the first table that is listed in the union query?

Phil

0 Votes 0 ·