Re: Today and Yesterday

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

> 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
>
>
> RogueIT wrote:
> > 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;
> > *******************************************
>
.

Relevant Pages

  • Re: general question about how best to cache expensive query results
    ... feeds and sometimes don't include the field ... That was my first attempt to limit the entries ... would be the result of that original query. ... presenting the underlying problem and the business requirements. ...
    (comp.databases)
  • general question about how best to cache expensive query results
    ... The question is that when I have a query like: ... entries to those that are newer than a certain date, ... ascending order in order of creation and find the highest id that's at ... as well as dealing with values having different types. ...
    (comp.databases)
  • Re: Access Table Sorting/Query Last Function
    ... I've put in a number of test entries to ensure it works, ... of the Lastin your totals query. ... I've been designing a database that basically works off two tables. ... I've done this so that I can pull the last entered ...
    (microsoft.public.access.tablesdbdesign)
  • Re: ComboBox
    ... >My combobox is based on a query that links 5 tables together. ... if I had 7 entries for the other ... >the query shows the 7 entries as 49 entries because of the table structure. ... recordsource property; and it can have only one Row Source. ...
    (microsoft.public.access.gettingstarted)
  • Re: SQL Server - Very long query time with OpenJPA and Hibernate
    ... I have a table with close to a million entries. ... Query Analyzer to obtain results within say 3 seconds. ... That's on a production server, ... that the program is the only thing accessing the database server. ...
    (comp.lang.java.databases)