Query from two separate databases

[Supe]

I had a database that had shipment data from 2007 to current where I would
run a query that would give me shipment totals since 2007. I have since hit
the 2GB max in my database so I moved the 2007 data to a table on a separate
database. Is there a way for me to get shipment totals if my data is in two
different databases?

Thought of linking both tables into another database, but how would I get
the shipment totals from two separate tables from one query????

 

[Beetle]

Just over two years worth of shipment data does not, on the surface
anyway, seem like it would be enough to max out an Access database.

Is your database split?

Do you Compact & Repair regularly?

If your database is split, and you really are at 2G after C&R, then you can
create another back end for the archived data and link the same front
end to multiple back ends.

 

[John W. Vinson]

I had a database that had shipment data from 2007 to current where I would
run a query that would give me shipment totals since 2007. I have since hit
the 2GB max in my database so I moved the 2007 data to a table on a separate
database. Is there a way for me to get shipment totals if my data is in two
different databases?

Thought of linking both tables into another database, but how would I get
the shipment totals from two separate tables from one query????

I'm with Beetle: this is not very plausible for normal data! Are you storing
pictures or OLE objects in the database? Or do you really have tens of
millions of shipment records? How big are these tables, in terms of the number
of records and the number of bytes per record?

You can (at the price of some VERY slow queries) use a single frontend with
links to both backends, and a UNION query splicing the two linked tables
together.

 

[Supe]

One year of data is about 5 million lines so it's a pretty big table. Think
I got a query to work. Thanks.

 

[John W. Vinson]

One year of data is about 5 million lines so it's a pretty big table. Think
I got a query to work. Thanks.

5 million rows x 40 bytes per row (a WAG of course) = 0.2 GByte. You've got
room for ten times that!

Is each row pushing 400 bytes? That suggests that you may want to do some more
normalizing.

Or, of course, you could and should consider moving the data into a SQL/Server
backend; properly designed this should be more robust and will get you away
from the 2Gbyte limit altogether.