microsoft query problem

[mikael3432]

hey

I dont know how to solve this problem.
I got a big table with like 50,000 rows like this:

person from update (title row)
name date date
name date date
name date date
...

with multiple rows for each person. I cant reduce the table in access because the date should be flexible:
from <=[date]
where date is a parameter
and after that I want to group by name with latest update row.

The table is so big so I get some overflow message from MS query when I try to do this. Anything I can do to solve this?

[gershwyn]

Can you post the complete SQL you are trying to run, as well as the exact error message you are receiving?

50,000 records isn't that many when it comes to a database (I often work with tables that have over a million.) I suspect the overflow is being caused by something else.

[dsatino]

I don't think you've really given anyone enough to help you. But here's some stuff I can tell you. 50,000 rows is not a lot from database standpoint. Dates, however do take a lot of memory so it's possible that this is causing the overflow error. Although I've only seen this error with variables. You might also want to check the size of your DB. If it's anywhere near 2GB you'll need to run a Compact/Repair.

Realistically, this table should probably be two tables. One with the Person and the other with the dates. This will allow the person to be listed only once.

My best guess at what you want:
Your Query should Group on the person and Max(date)

[mikael3432]

I think I can solve this with pivot table instead. easier
thanks anyway