Query or Table Issue

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
506
Afternoon

I have a table that holds an open and closed date in 2 seperate columns with a name in another and a column that states open or closed.
Is there a way I can create a query that will give me in seperate columns the following

Name - Raised Yesteday - Closed yesterday

I know how to create a query, but cant get both as I am unsure how to use the expression builder

All help appreciated
Thanks
Gavin
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
Will each unique name only have one Open record and one Closed record?
 

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
506
each record is a unique one in the table with a heading for
Agent - Type - Open On - Closed On - Pended - status

So I want the report/query to give me information of how many opened yesterday and closed yesterday.
This would be identifiable by checking the status.
[type] ="Enquiry" + [Open]=Date()-1 + [Status]<>"Closed" would give the total for opened enquiries yesterday
[type] ="Enquiry" + [Closed On]=Date()-1 + [Status]="Closed" would give the total for closed enquiries yesterday
But I then need to show the same for "Complaints" as well if possible all together.

Hope this helps
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
I think it would be even more helpful if you could post a small sampling of data, and your expected results.
Data and data structure is so important here, so it would be great to see that.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
Do you need to bring the status into it? In your criteria if an enquiry was opened and closed yesterday it would count as closed but not as raised. Surely if it has an opened on date of yesterday that is when it was raised and if it has a closed on date that is when it was closed?

Assuming your dates are stored as dates something like the following should work

Code:
SELECT Agent, Sum(IIf(([Type]="Enquiry" And [Open on]=Date()-1),1,0)) AS [Raised Yesterday], Sum(IIf(([Type]="Enquiry" And [Closed on]=Date()-1),1,0)) AS [Closed Yesterday]
FROM MyTable
GROUP BY Agent;
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,758
I'm probably missing something(s) but looking at the expressions...

If the presence of a ClosedDate means it's closed, why worry about a status of Closed as well?
... the total for opened enquiries yesterday is this not the count of all that were opened yesterday, regardless of when it was closed?
... the total for closed enquiries yesterday is this not the count of all that were closed yesterday, regardless of when it was opened?
Maybe consider a crosstab query so that you can get counts grouped by type.

Wondering if the posted table/field names are for real? There are a couple of reserved words being used (type, open).
 
Last edited:

Forum statistics

Threads
1,081,680
Messages
5,360,498
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top