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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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.
 

cmcreynolds

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

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
Them both being housed in the same table is what's confusing me with writing the query.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Can you post a small sample of what the original table looks like, showing examples of both Credits and Certificates?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,584
Office Version
2013
Platform
Windows
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.
 

cmcreynolds

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

cmcreynolds

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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;
 

cmcreynolds

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

Forum statistics

Threads
1,085,490
Messages
5,383,967
Members
401,868
Latest member
herbalgirlskincare

Some videos you may like

This Week's Hot Topics

Top