Retrieving past data

[mikesal57]

Hi Guys..



Quick question :

I have 2 tables , TODAY and HISTORY

Is there a way that access can start with the first name in TODAY and pull up last 3 records in HISTORY ...then list its data in all 3

Then continue with next name and repeat till it hits last name?

I believe its something like "Advance Filter" in Excel

Thxs

[CJ_London]

Suggest show some example data to illustrate your description if you want more than a hint of the solution

To get the last 3 you use the TOP predicate ordering by a date descending or similar and probably with a criteria to limit to a product type or similar.

You may also need to consider using a cross tab query depending on the requirement

Access is not excel and works in a completely different way and from your description I would have to query your design - if the two tables are effectively the same (i.e. tomorrow you transfer all the records from today into history) then there is no need to have two tables, one will do. You just control the view using queries and forms.

[mikesal57]

thxs CJ....

I didn't forget you...just need to run out for a while...thxs

I look up that cross tab query..its a start

[Micron]

I suspect all of that should be in one table, not two. To archive a record, you'd have a date field. If the field is not null, it is archived (history).

[ArviLaanemets]

Have your data in tables something like
tblNames: NameID, ForeName, LastName
tblYourData: YourDataID, NameID, SomeData1, SomeData2, ...,RecordDate

Have a continous form filtered by NameID with recordsource as query like

SELECT TOP 3 n.ForeName, n.LastName, d.SomeData1, ..., d.RecordDate FROM tblYourData d INNER JOIN tblNames n ON n.NameID = d.NameID ORDER BY d.YourDate DESC

User selects name in unbound combo on form, and afterupdate event of combo sets the filter property of form to NameID of selected name, and sets the form filter ON. The form displays last 3 entries for this name with last entry (i.e. current one) at top.

Any record in table, which doesn't have topmost RecordDate for given NameID, is archive record.

With such design, you also can design an UDF, which returns the active date for any given NameID at any given date. Consider possibilities using something like this!

[madpiet]

Hi Guys..

Quick question :

I have 2 tables , TODAY and HISTORY

Is there a way that access can start with the first name in TODAY and pull up last 3 records in HISTORY ...then list its data in all 3

Then continue with next name and repeat till it hits last name?

I believe its something like "Advance Filter" in Excel

Thxs

Some thing like this... failing that, look up Allen Browne's TOP N per Group on his website www.allenbrowne.com.au (I think - he's a former Access MVP)

SELECT t.PersonID, t.FName, t.LName, h.*
FROM tblToday as t
LEFT JOIN (SELECT TOP 3 * FROM History h
ORDER BY SomeDate DESC) ht
ON t.PersonID = ht.PersonID
ORDER BY t.FName, t.LName;

[isladogs]

Allen Browne's website is http://www.allenbrowne.com.
The Top N per group example is at http://www.allenbrowne.com/subquery-01.html#TopN

As others have already said it may be easier if the data is in one table not two

[madpiet]

Some thing like this... failing that, look up Allen Browne's TOP N per Group on his website www.allenbrowne.com.au (I think - he's a former Access MVP)

SELECT t.PersonID, t.FName, t.LName, h.*
FROM tblToday as t
LEFT JOIN (SELECT TOP 3 * FROM History h
ORDER BY SomeDate DESC) ht
ON t.PersonID = ht.PersonID
ORDER BY t.FName, t.LName;

While you could do something like

SELECT col1, col2, col3
FROM tblToday
UNION ALL
SELECT col1, col2, col3
FROM tblHistory

I think you lose the benefit of indexing when you do that. (I could be wrong, though). Stay away from plain UNION because it does a SORT and a DISTINCT under the covers.