Count data by column (1 Viewer)

bd528 · Sep 25, 2017

Hi all,

The following gives me a count for a specific date

Code:

SELECT Count(d.DATE_SENT_TO_REGISTRATIONS)
FROM tbldata d
WHERE (((d.DATE_SENT_TO_REGISTRATIONS)=#9/25/2017#));

How do I need to adapt this so I have 2 columns returned, and the second column's data being for the date 9/24/2017?

isladogs · Sep 25, 2017

Do you mean

Code:

SELECT Count(d.DATE_SENT_TO_REGISTRATIONS)
FROM tbldata [COLOR="Red"]AS[/COLOR] d
WHERE (((d.DATE_SENT_TO_REGISTRATIONS)=#9/25/2017#));

As you're only using one table you can simplify this:

Code:

SELECT Count(DATE_SENT_TO_REGISTRATIONS)
FROM tbldata 
WHERE (((DATE_SENT_TO_REGISTRATIONS)=#9/25/2017#));

However, do you ALWAYS want the query to be for this fixed date?

How do I need to adapt this so I have 2 columns returned, and the second column's data being for the date 9/24/2017?

Do you want a count for the 2nd date or the actual data?
For the 2nd column, will it ALWAYS be for that date or for the day BEFORE the date used in column 1?

Can you mock up an example of what you're expecting to see

bd528 · Sep 25, 2017

ridders said:
Do you want a count for the 2nd date or the actual data?

Honestly, I just used those dates as an example. If I was fortunate enough to get an answer, I was going to apply the methodology to what I require.

That aside, I need "today" and "yesterday". So,

Today Yesterday
123 456

Thanks

isladogs · Sep 25, 2017

The easiest way is probably to create 2 queries
The first one uses =Date in the criteria
The second one has =Date-1

Then create a third query and join these together

There are other ways but that's probably the easiest to understand.

bd528 · Sep 25, 2017

ridders said:
The easiest way is probably to create 2 queries
The first one uses =Date in the criteria
The second one has =Date-1

Then create a third query and join these together

There are other ways but that's probably the easiest to understand.

Great - thank you

Count data by column (1 Viewer)

bd528

Registered User.

isladogs

MVP / VIP

bd528

Registered User.

isladogs

MVP / VIP

bd528

Registered User.

Similar threads

Users who are viewing this thread