Query between dates with totals and amounts output (1 Viewer)

hesykhia

New member
Local time
Today, 13:03
Joined
Mar 15, 2016
Messages
8
I just started learning Access last week. I've built all my tables and forms, but the queries are majorly tripping me up, so any help you can give would be appreciated! I'm sure this has been asked a million different times, but I've been searching for hours and haven't been able to find an answer to my specific situation.

I have a table called "Holds Table". From that table, I am trying to create a query that will give me the total number of entries from a specific time period (it will change, so I am currently using parameters for that). Once I have that amount of entries, I need to have the query filter out Declines (a option group of "1" or "2") resubmissions (a checkbox), and calculate the amount of Disputed Dollars for JUST the entries provided. Everything I have LOOKS correct, but it isn't pulling anything back at all, so I know something is wrong. :banghead:


Here's the SQL view of what I've put together.

SELECT Count([Holds Table].[SHU Decision]) AS [CountOfSHU Decision], [Holds Table].[Resubmitted?], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
FROM [Holds Table]
WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
GROUP BY [Holds Table].[Resubmitted?]
HAVING (((Count([Holds Table].[SHU Decision]))=1) AND (([Holds Table].[Resubmitted?])=False));


What am I doing wrong? How can I fix it? Once I know/understand the problems here, I can apply this to the million other queries I need to write that basically call for variations the same thing, and link everything to a report.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:03
Joined
Aug 30, 2003
Messages
36,118
If it's not bringing anything back, the implication is that there aren't any records within the date range you specify. Can you attach the db here?
 

hesykhia

New member
Local time
Today, 13:03
Joined
Mar 15, 2016
Messages
8
Unfortunately I can't attach the db because it has confidential information in it, but I have several different test entries with all varieties of criteria, so it should definitely be pulling something back. I can try to explain what is in those a little bit better.

The date on all my entries is between 3/1/16 and 3/13/16. For my parameters I'm typing "1/1/16" and "3/15/16". Therefore, I should be returning ALL of my entries. If I run it with just this, and one other column that's just a "Group by", since this column shouldn't be visible, it works.

I need a total from the SHU Decision column. Like I said, it's a option group, with just two options. "Approved" which is "1" and "Declined" which is "2". So, I have "Count" in the Total row. I have several entries that are "1" and several entries that are "2". If I run the query with the aforementioned date parameters, it doesn't pull anything back at all, and it definitely should.

I also need to know if it has been resubmitted or not, which is a checkbox option. It's listed as a Group by, with the criteria as "False". I have several entries that are checked, and several entries that are not.

Finally, I need the total disputed dollar amount of all the entries that meet the criteria thus far. This is listed as "Sum". Every entry has a dollar amount.

I'm going to tinker around a little bit more, maybe it's just the type of "Total" that is the issue.
 

hesykhia

New member
Local time
Today, 13:03
Joined
Mar 15, 2016
Messages
8
And....after all that, I think I just figured it out. Sheesh. I changed the SHU Decision column to "Group by" instead of "Count", then added another "Date email received" column, selected "Count" on that one, and it's pulling back the right info.

Well, thanks for being my sounding board, guys. Looks like I just needed to actually type it out for me to find my issue. I'm going to post the SQL just in case someone else ever encounters a similar issue.

SELECT Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
FROM [Holds Table]
WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
GROUP BY [Holds Table].[SHU Decision], [Holds Table].[Resubmitted?]
HAVING ((([Holds Table].[SHU Decision])="1") AND (([Holds Table].[Resubmitted?])=False));
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:03
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted out.
 

Users who are viewing this thread

Top Bottom