Query or Table Issue

[gmazza76]

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]

Will each unique name only have one Open record and one Closed record?

 

[gmazza76]

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]

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]

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

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]

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).