Today and Yesterday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

TableA has a listing of several companies that are imported daily. How would
I set the cirtieria of of tableA.date in a query to show me all the companies
that have a date field of today and yesterday or now and now minus one day?
Thanks in advance
RogueIT
 
now()-1

Or, if you only need to worry about the date part of the value:

date() - 1

When working with Date/Time (but not Date only) values, you can add/subtract
in units of days or fractional parts thereof. So, now() - 0.5 would be
equivalent to 12 hours earlier.



Good Luck!
 
How would I have it only show me only the companies that have entries in the
table for today and yesterday (technically it would be yesterday and day
before that ) so companies that have consecutive days from yesterday
*******************************************
SELECT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name ], Last(Information_coll.Date) AS LastOfDate
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID ] =
Count_ID.[ID ]
GROUP BY Count_ID.[ID ], Count_ID.CountOfID, Information_coll.[Company Name ]
HAVING (((Last(Information_coll.Date))=Date()-1 Or
(Last(Information_coll.Date))=Date()-2))
ORDER BY Count_ID.CountOfID DESC;
*******************************************
 
RogueIT,

I gather that the query you posted does not give the expected results?
In what way is it different from expected?

It is not clear what the purpose of the GROUP BY clause in the query is.
Nor is it clear what the role of the fields from the Count_ID query
play, but I imagine there is only one record returned in this query for
each company, yes? Also, 'count' and 'date' are both Reserved Words
(i.e. have a special meaning) in Access, and as such should not be used
as the name of fileds or controls... but that is unlikely to be causing
the problem. The Last() function returns a random record, probably you
need Max(). Is the data in the [Date] field just a date, or does it
include time data as well? Assuming just a date:

I would try it like this...
SELECT DISTINCT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name], Information_coll.[Date]
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID] =
Count_ID.[ID]
WHERE Information_coll.[Date]=Date()-1 Or Information_coll.[Date]=Date()-2
ORDER BY Count_ID.CountOfID DESC;

or...
SELECT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name ], Max(Information_coll.[Date]) AS MaxOfDate
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID] =
Count_ID.[ID]
GROUP BY Count_ID.[ID], Count_ID.CountOfID, Information_coll.[Company Name]
HAVING ((Max(Information_coll.[Date]))=Date()-1 Or
(Max(Information_coll.[Date]))=Date()-2)
ORDER BY Count_ID.CountOfID DESC;

If neither of these help, please let us know what the problem is.
 
I have several days worth of data, companies will have multiple entries into
the Information_coll.
I use this query to show me how many entries are in table Information_coll
So if id is in information_coll 5 times it will show me the number 5 in the
front-end query.
******************count of ID**********************************
SELECT Information_coll.[ID ], Count(Information_coll.[ID ]) AS CountOfID
FROM Information_coll
GROUP BY Information_coll.[ID ];
************************************************************
The preferred result would be if a company has an entry from yesterday and
day before, but no other entries in the table.
How would I set the criteria on the information_coll.date field to show me
that?
Examples of what I don't want to see are
Id with a count of 1 or 3 or >3
Id's with date entries before 2 days ago.

Right now I am getting all entries with a date = date() -1, so if a company
has 4 entries but one of the entries = date() -1 then I get them in the
results...

I never have been a good explainer, if there is some more date that I can
give you to help me just let me know.

Thanks,
RogueIT

Steve Schapel said:
RogueIT,

I gather that the query you posted does not give the expected results?
In what way is it different from expected?

It is not clear what the purpose of the GROUP BY clause in the query is.
Nor is it clear what the role of the fields from the Count_ID query
play, but I imagine there is only one record returned in this query for
each company, yes? Also, 'count' and 'date' are both Reserved Words
(i.e. have a special meaning) in Access, and as such should not be used
as the name of fileds or controls... but that is unlikely to be causing
the problem. The Last() function returns a random record, probably you
need Max(). Is the data in the [Date] field just a date, or does it
include time data as well? Assuming just a date:

I would try it like this...
SELECT DISTINCT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name], Information_coll.[Date]
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID] =
Count_ID.[ID]
WHERE Information_coll.[Date]=Date()-1 Or Information_coll.[Date]=Date()-2
ORDER BY Count_ID.CountOfID DESC;

or...
SELECT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name ], Max(Information_coll.[Date]) AS MaxOfDate
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID] =
Count_ID.[ID]
GROUP BY Count_ID.[ID], Count_ID.CountOfID, Information_coll.[Company Name]
HAVING ((Max(Information_coll.[Date]))=Date()-1 Or
(Max(Information_coll.[Date]))=Date()-2)
ORDER BY Count_ID.CountOfID DESC;

If neither of these help, please let us know what the problem is.

--
Steve Schapel, Microsoft Access MVP

How would I have it only show me only the companies that have entries in the
table for today and yesterday (technically it would be yesterday and day
before that ) so companies that have consecutive days from yesterday
*******************************************
SELECT Count_ID.[ID ], Count_ID.CountOfID AS [Count],
Information_coll.[Company Name ], Last(Information_coll.Date) AS LastOfDate
FROM Information_coll INNER JOIN Count_ID ON Information_coll.[ID ] =
Count_ID.[ID ]
GROUP BY Count_ID.[ID ], Count_ID.CountOfID, Information_coll.[Company Name ]
HAVING (((Last(Information_coll.Date))=Date()-1 Or
(Last(Information_coll.Date))=Date()-2))
ORDER BY Count_ID.CountOfID DESC;
*******************************************
Click to expand...
Click to expand...
 
RogueIT,

Just to make sure I understand what you want the query to do for you...

Examples:

CompanyA: 1 entry in table, 20-Jul
CompanyB: 1 entry in table, 19-Jul
CompanyC: 2 entries in table, 19-Jul and 20-Jul
CompanyD: 2 entries in table, 18-Jul and 19-Jul
CompanyE: 3 entries in table, 18-Jul and 19-Jul and 20-Jul

The query should only return CompanyC, right?
 
My god...so simple...yes that is what I am looking for but it has to be dynamic
for example tomorrow I will only want to see the
companies that have 2 entries in table, jul-20 and jul-21
that is correct...thank you so much.




Steve Schapel said:
RogueIT,

Just to make sure I understand what you want the query to do for you...

Examples:

CompanyA: 1 entry in table, 20-Jul
CompanyB: 1 entry in table, 19-Jul
CompanyC: 2 entries in table, 19-Jul and 20-Jul
CompanyD: 2 entries in table, 18-Jul and 19-Jul
CompanyE: 3 entries in table, 18-Jul and 19-Jul and 20-Jul

The query should only return CompanyC, right?

--
Steve Schapel, Microsoft Access MVP
I have several days worth of data, companies will have multiple entries into
the Information_coll.
I use this query to show me how many entries are in table Information_coll
So if id is in information_coll 5 times it will show me the number 5 in the
front-end query.
******************count of ID**********************************
SELECT Information_coll.[ID ], Count(Information_coll.[ID ]) AS CountOfID
FROM Information_coll
GROUP BY Information_coll.[ID ];
************************************************************
The preferred result would be if a company has an entry from yesterday and
day before, but no other entries in the table.
How would I set the criteria on the information_coll.date field to show me
that?
Examples of what I don't want to see are
Id with a count of 1 or 3 or >3
Id's with date entries before 2 days ago.

Right now I am getting all entries with a date = date() -1, so if a company
has 4 entries but one of the entries = date() -1 then I get them in the
results...

I never have been a good explainer, if there is some more date that I can
give you to help me just let me know.

Thanks,
RogueIT
Click to expand...
Click to expand...
 
RogueIT,

Well, here's a relatively straightforward way of doing it...

1. Make a query to give you yesterday's companies. The SQL for this
query will look something like this:
SELECT Information_coll.[ID]
FROM Information_coll
WHERE Information_coll.[YourDate] = Date()-1

2. Make a query to give you the day before yesterday's companies. The
SQL for this query will look something like this:
SELECT Information_coll.[ID]
FROM Information_coll
WHERE Information_coll.[YourDate] = Date()-2

3. Make another query, which includes your original query, plus both of
the above queries joined to it on the ID field.

I have assumed that any given company will not have more than one entry
in the table on any given day. Is that right? If not, you will need to
use the DISTINCT keyword in the queries above, i.e. SELECT DISTINCT
Information_coll.[ID] etc.
 
Back
Top