General Query structure question

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
At what point is it crashing?
How exactly are you trying to "join" them?
Which fields are you joining on?

What probably makes the most sense is for you to post a small data sample of the data you are working with, and then what you want your expected results to look like.
 
Upvote 0
Okay. Here goes (sorry again if I get confusing):

I have one query to get a sum of the units and receive this from the table dbo_Activity:
IDACTIVITY_TYPECATEGORYUNITSTRANSACTION_DATE
135865
CEUF2F1.55/4/2014
135865
CEUF2F1.52/2/2016
135865
CEUF2F1.52/2/2016
135865
CEUF2F1.57/18/2016
135865
CEUF2F1.57/18/2016
118718
CEUF2F1.53/4/2014
118718
CEUF2F1.54/10/2014
118718
CEUF2F1.55/15/2014
118718
CEUF2F1.511/6/2014
189680
CEUF2F1.52/25/2014
189680
CEUF2F1.52/25/2014
189680
CEUF2F1.52/25/2014
189680
CEUF2F1.52/25/2014
189680
CEUF2F1.512/9/2015
159940
CEUF2F1.511/2/2015
159940
CEUF2F1.511/24/2014
159940
CEUF2F1.53/18/2015
159940
CEUF2F1.53/18/2015
159940
CEUF2F1.53/14/2016
159940
CEUF2F1.53/14/2016
159940
CEU
F2F1.53/14/2016
159940
CEUF2F1.510/7/2016
159940
CEUF2F1.510/7/2016
234639
CEUF2F1.53/24/2014
234639
CEUF2F1.53/25/2014

<tbody>
</tbody>

(I want to eventually aggregate/sum these by ID)

My second query is to see when they received their member certificate also in the table dbo_Activity:
IDACTIVITY_TYPEPRODUCT_CODETRANSACTION_DATE
118718
CERTIFICATMember4/3/2014
118718
CERTIFICATMember6/17/2015
135865
CERTIFICATMember4/13/2015
159940
CERTIFICATMember1/6/2015
234639
CERTIFICATMember4/25/2014

<caption> Query2 </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>

Note: Here, people can have more than one record and I'd like to use the MAX Aggregation to get the most recent date.

This is what I would WANT it to look like:

IDSumofUnits
MaxCertificateDate
118718
6
6/17/2015
135865
7.5
4/13/2015
159940
13.5
1/6/2015
234639
3
4/25/2014

<caption> Query2 </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>
 
Upvote 0
Can you post a small sample of what the original table looks like, showing examples of both Credits and Certificates?
 
Upvote 0
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.

In general, "two separate queries" will perform as well or better than "the same query". Would be useful to also know how many records are involved.
 
Upvote 0
Ok, I took out other fields (there are over 30) and this is only a small sample:
IDACTIVITY_TYPECATEGORYUNITSPRODUCT_CODETRANSACTION_DATE
123456
CERTIFICATMember12/21/2016
123456CEUF2F1.5CON93233/3/1993
456734CERTIFICATCON93303/3/1993
456734CEUF2F1.5CON93433/3/1993
456734CEUF2F1.5CON93543/3/1993

<tbody>
</tbody>


Does this help explain my dilemma?
 
Last edited:
Upvote 0
In general, "two separate queries" will perform as well or better than "the same query". Would be useful to also know how many records are involved.

This one table has over a million records. There are even more "Activity Types" than the two I'm listing here.
 
Upvote 0
Wouldn't this get you what you are looking for?
Code:
SELECT dbo_Activity.ID, Sum(dbo_Activity.UNITS) AS SumOfUNITS, Max(dbo_Activity.TRANSACTION_DATE) AS MaxOfTRANSACTION_DATE
FROM dbo_Activity
GROUP BY dbo_Activity.ID;
 
Upvote 0
Shoot, I forgot to add one caveat: The units expire. That's kind of what was tripping me up in the first place and I forgot to mention it. I am really sorry about that.

Joe, can I apply a SEPARATE date restriction to JUST the Units?

Also, like I said earlier there are about 35 other Activity Types, some use Units in other ways. Also, there are other types of CEUs in addition to F2F and other CERTIFICATs in addition to Member, but right now, I'm just interested in CEU/F2F and CERTIFICAT/Member.

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?

Thanks again for your help.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top