General Query structure question

cmcreynolds

Active Member
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
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
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>
 

Joe4

MrExcel MVP, Junior Admin
Can you post a small sample of what the original table looks like, showing examples of both Credits and Certificates?
 

xenou

MrExcel MVP, Moderator
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
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
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top