I'd like report to have "WHERE ([form field]=stuff)" popup

[Banaticus]

I've combined information from two tables, like so:
SELECT [Class Information].Date, [Class Information].[Class Name], [Class Information].[Credit?]
FROM [Class Information]
UNION SELECT [Note Information].Date, [Note Information].Note, [Note Information].[Eff Date]
FROM [Note Information];

Can I restrict the data by a field that hasn't been selected? [Class Information] and [Note Information] are both sub fields (one to many relationship) of the main customer table. Both [Class Information] and [Note Information] are ordered by a dependant customer number from the main customer table.

I'd like to do a query that uses the SQL statement above, but that asks for a customer number. It would then return only the class and note information that match the "customer number".

I would also like to order all of the information by Date. How would I do this, though, "ORDER BY [Note Information].Date AND [Class Information].Date"?

 

[SammyJ]

Do you really then need a UNION query? Do you want different types of information of different rows? What will you be doing with the information?

You are free to add WHERE clauses to your individual queries and if you need to JOIN in you main table you can do in each case. Then add a parameter to the query like WHERE [Customer].[Customer Number] = [Enter the customer number:] in both of them. If the parameter is identically worded in both it will only appear once.

If I recall you can only use ORDER BY in the last query of a UNION query and the field names are defined by the very first query, so in fact ORDER BY [Date] should work.

But look at the inital questions here first, and give it some thought.

Sam.