needing to use fields from unrelated tables

[nomojo]

I dont know if this is in the right spot, or if it is even possible.

I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, i just need to know how many.

Is this even possible?

 

[John Big Booty]

Perhaps DCount() will do what you are after.

 

[Pat Hartman]

To collect data from separate unrelated tables into a single query, use a union. Union queries must be built as SQL strings, the QBE doesn't support them. The easiest way to build them if you are not comfortable with SQL, is to use the QBE to build each individual query. So your Union would end up looking like:

Select * from qry1
Union Select * from qry2
Union Select * from qry3;

The individual select clauses or queries MUST select columns of the same data type in the same order or the union will flat out not work or will return invalid results. So for example:

Select firstname, lastname, DOB from tbl1
Select lastname, firstname, DOB from tbl2

Will not fail outright but will produce a mixture of first and last names in the first column and first and last names in the second column depending on the source of the data.

Select DOB, lastname from tbl1
Select lastname, DOB from tbl2

Will fail because the data types of the columns are not consistant.
Sometimes you want to know the source of the data. In that case add a dummy column with a text value;

Select ..., "History" as SourceCd From tblHistory
Union Select ..., "Current" as SourceCD From tblCurrent;

In summary, column name is irrelevant but data type and field order are critical.

 

[boblarson]

You can create a query for each to get the age breakdowns on the age field and then use a Union query to bring them together and then do a grouping query on those. See attached sample (the final query is named qryFINALQueryWithAgeBreakouts but look at everything to see how I put it together).

(Pat posted before I could upload my sample, but it looks like she was using almost the same method for my initial query, so the sample might still help with seeing how to do it). I did include how to get the distributions too.

 

[nomojo]

thank you both Pat and Bob, everything working. You saved me a lot of headaches.