General Query structure question

[cmcreynolds]

Happy Friday everyone -

I am honestly praying I explain this correctly:

I'm accessing a table in my organization's database and there are various activities recorded there I'm trying to summarize.

For use in my example, the one table is "dbo_Activity" and the first main distinction is the field "Activity_Type" I want to look at these two types:

"Credits" and "Certificates"

Credits has four fields that I look at - MemberID, Category, Units, and Transaction_Date. I use a "Totals" query to sum the units

Certificates has four I need to look at - MemberID, Product_Code and Transaction_Date. (this is normalized, correct?)
​

My general question is this: I want to query people with a certain number of Units after a certain date (CREDITS) AT THE SAME TIME as having a Product_Code with a Transaction_Date (CERTIFICATES). Is it more efficient to have two separate queries and then join the results? Or would Access "work better" if I had them in the same query?

I've tried using two separate queries, but it seems to crash Access every single time. That's why I was wondering if it would make more sense if I had it in the same query.

I have the SQL ready if you think that'd help explain things.

 

[Joe4]

Note that you can add criteria to the query I posted above simply by inserting a WHERE clause, i.e.

SELECT dbo_Activity.ID, Sum(dbo_Activity.UNITS) AS SumOfUNITS, Max(dbo_Activity.TRANSACTION_DATE) AS MaxOfTRANSACTION_DATE
FROM dbo_Activity
WHERE ...
GROUP BY dbo_Activity.ID;

If you want to further add Criteria to the Aggregated Values (Sum of Units or Max Date), you can add a HAVING clause after the GROUP BY clause.

I suppose a summary question would be asking: When I pull from the Activity table, can I aggregate two separate sums within the same query? Or is it better to do that aggregation separately in two queries and then combine them in a third?

You often can, but it depends on the details. I would need to see some examples of what you are after to see what we can do.

 

[cmcreynolds]

And if this is related - If I want to "SELECT" fields, do I also have to "GROUP BY" them? (like if I'm grabbing address, city, state, etc.)

 

[Joe4]

If you are using an Aggregate Query (i.e. "Group By"), every un-aggregated field in your SELECT clause would also need to be included in the GROUP BY clause.
Note, however, every fields included in your WHERE clause do NOT have to appear in the SELECT clause (and by extension, the GROUP BY clause).
In other words, you can use fields in criteria that are not being returned by your query.

 

[cmcreynolds]

If you are using an Aggregate Query (i.e. "Group By"), every un-aggregated field in your SELECT clause would also need to be included in the GROUP BY clause.
Note, however, every fields included in your WHERE clause do NOT have to appear in the SELECT clause (and by extension, the GROUP BY clause).
In other words, you can use fields in criteria that are not being returned by your query.

Good, that's what I thought.