VB Access Basic Question

[tmw2912]

I have two queries written in a module in Access that will open both queries. Is there a way to write code that will combine the two queries into a single output?

[mbar]

how about creating a 3rd query that will combine the original 2, then using code to open the 3rd query.

[tmw2912]

I don't think that would work. Let me try to explain in further detail to see if possible.

Query 1 -
Is a listing of all Employees and the information on their account. Those Employees also have people that are attached to them. The tables that I am pulling for Query 1 only give me the information for the employee themselves.

EX:
ID12345, John Smith, 1234 Main Street, Anytown, USA
ID67891, Michael Johnson, 5678 3rd ST, Jamestown, Africa

Query 2 -
Lists information for those attached to the Primary.

EX:

ID12345, SUBID98765, Amanda Smith, 1234 Main Street, Anytown, USA
ID67891, SUBID45687, Jeremy Johnson, 5678 3rd ST, Jamestown, Africa

What I am trying to get is a combination of the two queries that would look like the:

ID12345, SUBID98765, Amanda Smith, 1234 Main Street, Anytown, USA
ID67891, SUBID45687, Jeremy Johnson, 5678 3rd ST, Jamestown, Africa
ID12345, , John Smith, 1234 Main Street, Anytown, USA
ID67891, , Michael Johnson, 5678 3rd ST, Jamestown, Africa


Hopefully that makes more sense. Is it possible to do that?

[Xipooo]

It can be done as a third query. I'd make a Union query a subquery, then do an Order By clause to order your data for you.
SELECT SQ1.*
FROM (SELECT ID, "" AS SUBID, Name, Address1, City, State FROM Table1 UNION SELECT ID, SUBID, Name, Address1, City, State FROM Table2) as SQ1
Order By ID, SubID

[tmw2912]

Awesome, that worked. Thanks

[ipisors]

No offense, but I think the worst advice someone could possibly give you right now is to just keep on cruising along...and use a Union query to 'stack' data on top of each other, data that is resulting from non-normalized data.

If you had the proper tables set up, and the proper data in those tables, you should be able to do all of it in one query, or at the very worst, a 2nd query that uses the first query as a table (but I doubt that'd be necessary).

The best thing you can do for yourself at this point is to step back and do some re-design..truthfully.

[Xipooo]

No offense, but I don't really care to tell someone they need to completely re-organize their entire data structure which may have several thousands of rows of data in it already, just to make one simple query. Could the database possibly be re-organized for better normalization, sure. But I wouldn't say the time and effort it would take is warranted just because of one query. Perhaps the database was inherrited. Perhaps management demands it to be structured this way. Who knows. But it certainly isn't the worst advice someone can give.

[ipisors]

I suppose we can agree to disagree. I want to give out quality advice, and yes, very rarely there are situations where structures cannot be re-done. But my standpoint is, Let the OP tell you that they CAN'T do the right thing - don't jump for the lowest possible standard first off by assuming it..

It just seems like a very, very odd perspective from an 'experienced access developer', who would see this as a major red flag and give the restructuring advice.

But if you see the goal as finishing the query and cruising on, well then...