Results 1 to 9 of 9
  1. #1
    SabWit0622 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4

    Question Help forming these three queries please! I don't know what to put for criteria/totals.

    Never mind. Thank you for your help so far though, I appreciate it.
    Attached Thumbnails Attached Thumbnails Screenshot (18).png  
    Last edited by SabWit0622; 03-12-2021 at 12:58 PM. Reason: Providing Photo

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The field names are cardholdername, vendor, amount, transactiondate, posteddate, merchantcategorycode, description, year, month, and agencyname.

    Does that mean that purchaser is really cardholder??

    It might clarify things if you posted the SQL for your query attempts.

  3. #3
    SabWit0622 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4
    Yes, the purchaser refers to the cardholdername field. I just edited my initial post to reflect that. Sorry about that! I'm incredibly new to Access--this is actually my first time building queries.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No problem - we were all new to this at one time or another.

    Better if you provide data rather than a picture/graphic of data.

    Can you go to the SQLview of any queries you have tried; copy and post the Sql.

    Also, there is no need to separate year and month from your Dates.
    Access has Year() and Month() functions to provide these values.

  5. #5
    SabWit0622 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4
    For the first Query, this is the SQL. It's completely wrong, but I don't know how to make it show me transactions where there was the same cardholder and vendor more than once on the same day

    SELECT Sabrina.CardholderName, Sabrina.Vendor, Sum(Sabrina.Amount) AS SumOfAmount, Sabrina.TransactionDate
    FROM Sabrina
    GROUP BY Sabrina.CardholderName, Sabrina.Vendor, Sabrina.TransactionDate
    HAVING (((Sum(Sabrina.Amount))>5000) AND (Count(*)>1));

    I have not attempted the other two queries yet because I know I cannot do them if I can't even do the first one.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    SabWit0622 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    4
    ID AgencyNumber AgencyName CardholderLastName CardholderFirstInitial Description Amount Vendor TransactionDate PostedDate MCC Year Month CardholderName
    1 1000 OKLAHOMA STATE UNIVERSITY Mason C GENERAL PURCHASE $890.00 NACAS 7/30/2013 7/31/2013 CHARITABLE AND SOCIAL SERVICE ORGANIZATIONS 2013 7 Mason,C
    2 1000 OKLAHOMA STATE UNIVERSITY Mason C ROOM CHARGES $368.96 SHERATON HOTEL 7/30/2013 7/31/2013 SHERATON 2013 7 Mason,C
    3 1000 OKLAHOMA STATE UNIVERSITY Massey J GENERAL PURCHASE $165.82 SEARS.COM 9300 7/29/2013 7/31/2013 DIRCT MARKETING/DIRCT MARKETERS--NOT ELSEWHERE CLASSIFIED 2013 7 Massey,J
    4 1000 OKLAHOMA STATE UNIVERSITY Massey T GENERAL PURCHASE $96.39 WAL-MART #0137 7/30/2013 7/31/2013 GROCERY STORES,AND SUPERMARKETS 2013 7 Massey,T
    5 1000 OKLAHOMA STATE UNIVERSITY Mauro-Herrera M HAMMERMILL COPY PLUS COPY EA $125.96 STAPLES DIRECT 7/30/2013 7/31/2013 STATIONERY, OFFICE SUPPLIES, PRINTING AND WRITING PAPER 2013 7 Mauro-Herrera,M
    6 1000 OKLAHOMA STATE UNIVERSITY Mauro-Herrera M GENERAL PURCHASE $394.28 KYOCERA DOCUMENT SOLUTION 7/29/2013 7/31/2013 OFFICE, PHOTOGRAPHIC, PHOTOCOPY, AND MICROFILM EQUIPMENT 2013 7 Mauro-Herrera,M
    7 1000 OKLAHOMA STATE UNIVERSITY Mays B GENERAL PURCHASE $19.87 WAL-MART #0137 7/30/2013 7/31/2013 GROCERY STORES,AND SUPERMARKETS 2013 7 Mays,B
    8 1000 OKLAHOMA STATE UNIVERSITY Mays B GENERAL PURCHASE $10.10 DAYLIGHT DONUTS 7/29/2013 7/31/2013 FAST FOOD RESTAURANTS 2013 7 Mays,B
    9 1000 OKLAHOMA STATE UNIVERSITY McCarthy D GENERAL PURCHASE $142.60 UFIRST LAUNDRY SVCS 7/30/2013 7/31/2013 MENS, WOMENS AND CHILDRENS UNIFORMS AND COMMERCIAL CLOTHING 2013 7 McCarthy,D
    10 1000 OKLAHOMA STATE UNIVERSITY McClurg L AT&T SERVICE PAYMENT ITM $29.99 AT&T DATA 7/30/2013 7/31/2013 TELECOMMUNICATION SERVICES 2013 7 McClurg,L
    11 1000 OKLAHOMA STATE UNIVERSITY McGowin R GENERAL PURCHASE $278.23 RANDY'S FOODS 7/29/2013 7/31/2013 FREEZER AND LOCKER MEAT PROVISIONERS 2013 7 McGowin,R
    12 1000 OKLAHOMA STATE UNIVERSITY McKee P GENERAL PURCHASE $127.80 DEARINGER PRINTING & TROP 7/30/2013 7/31/2013 MISCELLANEOUS AND SPECIALTY RETAIL STORES 2013 7 McKee,P
    13 1000 OKLAHOMA STATE UNIVERSITY McLemore A GENERAL PURCHASE $572.27 GIDDINGS MACHINE CO. 7/30/2013 7/31/2013 COMMERCIAL EQUIPMENT, NOT ELSEWHERE CLASSIFIED 2013 7 McLemore,A
    14 1000 OKLAHOMA STATE UNIVERSITY McMurry S 50-LB PLAY SAND BAG|20 QT POTTING SOIL TIMBERL EA $12.90 LOWES #00241 7/30/2013 7/31/2013 HOME SUPPLY WAREHOUSE STORES 2013 7 McMurry,S
    15 1000 OKLAHOMA STATE UNIVERSITY Meints K GENERAL PURCHASE $445.00 OK DEPT OF VO-TECH ED 7/30/2013 7/31/2013 GOVERNMENT SERVICES--NOT ELSEWHERE CLASSIFIED 2013 7 Meints,K
    16 1000 OKLAHOMA STATE UNIVERSITY Mohiuddin A GENERAL PURCHASE $452.91 TFS THERMOASHEVILLE 7/31/2013 7/31/2013 DENTAL/LABORATORY/MEDICAL/OPHTHALMIC HOSP EQIP AND SUP. 2013 7 Mohiuddin,A
    17 1000 OKLAHOMA STATE UNIVERSITY Mohiuddin I GENERAL PURCHASE $2,425.00 WESTERN EQUIPMENT ALVA 7/29/2013 7/31/2013 HARDWARE STORES 2013 7 Mohiuddin,I
    18 1000 OKLAHOMA STATE UNIVERSITY Mohiuddin I GENERAL PURCHASE $2,425.00 WESTERN EQUIPMENT ALVA 7/29/2013 7/31/2013 HARDWARE STORES 2013 7 Mohiuddin,I
    19 1000 OKLAHOMA STATE UNIVERSITY Mollenhauer R GENERAL PURCHASE $7.63 HOBBY-LOBBY #0005 7/29/2013 7/31/2013 HOBBY,TOY,AND GAME STORES 2013 7 Mollenhauer,R
    20 1000 OKLAHOMA STATE UNIVERSITY MONTGOMERY R GENERAL PURCHASE $18.97 WAL-MART #0121 7/30/2013 7/31/2013 GROCERY STORES,AND SUPERMARKETS 2013 7 MONTGOMERY,R
    These are the first twenty records as plain text.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A few questions:

    Is this a school/homework question(s)?

    Please post the criteria for the 3 queries.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Code:
    SELECT Sabrina.CardholderName
    , Sabrina.Vendor
    , Sum(Sabrina.Amount) AS SumOfAmount
    , Sabrina.TransactionDate
    FROM Sabrina
    GROUP BY Sabrina.CardholderName, Sabrina.Vendor, Sabrina.TransactionDate
    HAVING (((Sum(Sabrina.Amount))>1000) AND (Count(*)>1));
    There are no records in these 20 records such that Sum(Sabrina.Amount))>5000.
    So, I modified the sql to use 1000, and got 1 cardholder
    CardholderName Vendor SumOfAmount TransactionDate
    Mohiuddin,I WESTERN EQUIPMENT ALVA $4,850.00 7/29/2013


    Because of the Grouping you will not get detailed records.

    Count of Transactions By CardholderName by Date

    CardholderName TransactionDate CountOfCardholderName
    Mason,C 7/30/2013 2
    Massey,J 7/29/2013 1
    Massey,T 7/30/2013 1
    Mauro-Herrera,M 7/29/2013 1
    Mauro-Herrera,M 7/30/2013 1
    Mays,B 7/29/2013 1
    Mays,B 7/30/2013 1
    McCarthy,D 7/30/2013 1
    McClurg,L 7/30/2013 1
    McGowin,R 7/29/2013 1
    McKee,P 7/30/2013 1
    McLemore,A 7/30/2013 1
    McMurry,S 7/30/2013 1
    Meints,K 7/30/2013 1
    Mohiuddin,A 7/31/2013 1
    Mohiuddin,I 7/29/2013 2
    Mollenhauer,R 7/29/2013 1
    MONTGOMERY,R 7/30/2013 1

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 08-31-2016, 04:11 PM
  2. Forming Access Query!
    By Shubham Aggarwal in forum Access
    Replies: 2
    Last Post: 02-23-2015, 01:58 AM
  3. Need help forming an If-Then-Else Statement
    By tlkng1 in forum Programming
    Replies: 1
    Last Post: 11-05-2014, 10:54 AM
  4. Report using totals from 2 queries...
    By TSALauren in forum Reports
    Replies: 29
    Last Post: 08-20-2012, 02:58 PM
  5. Graph totals queries with different data
    By maggioant in forum Queries
    Replies: 0
    Last Post: 10-01-2009, 12:12 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums