Query across multiple databases

[resumes123]

I am using MS Access 2007
I have 12 different databases for 12 different months ( Jan to Dec '08)
Each database has exactly one table called "Daily_Trans". All databases are exactly alike
Daily_Trans table has the sales transactions of the company on a daily and hourly basis.
The fields in the table are Date , Time, price , quantity_sold
For every day for example Jan 1, 2008, it has transactions at 9:01, 9:02 , 9:03 AM.... till 5:00 PM
and the transactions are the sales transactions for each minute and hour for that day and the price and quantity sold at that time.
January database has all 31 days of January '08, February database has all 28 days of February database and so forth.
At any given instant I can query only one month within a given database.
I need to get the aggregated sum of quantity, price , maximum price and average price

The current query I am writing is
Select max(price),average(price), sum(price) from daily_trans group by date.

Currently, my manager wants to query across multiple dates in multiple months
Is there a way I can query data from Jan 1, 08 to April 30 or ( Feb 17 to Sep 9 )

That would mean that I need to open Jan database, Feb Database, Mar database and Apr database to execute the first query
Could some one suggest a method or VBA procedure code so that I could query across multiple databases. All my databases are in " C:\Sales " folder


thanks

 

[dhookom]

You can create linked tables from multiple MDB files. Then create a union query against the linked tables.

You didn't provide any justification for creating multiple MDBs. To most of this it doesn't make any sense. If your MDBs would be too large, consider dumping Access as a back-end and using SQL Server or SQL Server Express.

Duane
Hook'D on Access
MS Access MVP

 

[resumes123]

Thank you for your post

It was more of a business decision that the databases be maintained in 12 different databases instead of one database. This is because each database is of 1 GB of size and the total size of all these databases is 12 GB
MS Access 2007 can have a maximum database size of 2GB at any given instant


Thanks for the suggestion that I should do a UNION.

If I am querying 3 months then it is union of 3 months ( Jan, Feb and MAr )
But my query is always dynamic,
Today it could be from Feb 19 to SEp 6, tomorrow it could be Apr 30 to Dec 15
or Jun 25 to Aug 19
It was always changing. In such changing scenario, how could I capture, which months I need to query and how could I write a MS Access VBA code to write a UNION query

thanks for your cooperation

 

[dhookom]

You can keep a saved query with a name like "quniMonths". Then a little DAO code can change the SQL property to a different statement.

Dim strSQL as String
strSQL = "SELECT 1 As Mth, fieldA, fieldB, fieldC,.. " & _
   "FROM tblJan UNION ALL " & _
   "SELECT  2, fieldA, fieldB, fieldC,.. " & _
   "FROM tblFeb"
Currentdb.QueryDef("quniMonths").SQL = strSQL

If you have that much sales data, you should invest in SQL Server. SQL Server Express is free and powerful.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

I would second Duane. Even though SQL Express still has file size limits, it would be much more robust than Access since it's true client/server. You could keep your Access front end.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[lameid]

Looks like SQL Express 2008 has a file limit of 4GB. While there may be some size savings in going SQL Express, I doubt it is 66%.

That said, ideally move to SQL server.

Otherwise what Duane is saying about unions is your only real option. You could maintain a table that has all the tables and months in them and dynamically create a union query via code to give you the base Union SQL statement. Then just change the SQL statment of the base Union query (don't share your front end). This would save you from including tables that you do not need to in your Union for execution which should speed things up.

If you are really only looking at averages and other summary funtions you might consider storing the average and count of values of each table. Then you could UNION the average, count and average * count so that you include the overall averages and the partials in a table. Then base a query based off this and take the sum of the average * count field and divide by count to arrive at an average. This is somewhat of a maintenance nightmare but should be a lot closer to usable performancewise.

The real answer really is SQL SERVER and a single table.

 

[SeeThru]

I would union all the tables in a singe query, and then base a query on that query that includes a selector for the month.

SeeThru
Synergy Connections Ltd - Uk Telemarketing and Telesales Services and
Synergy Mobile Solutions - UK Mobile phones, land lines and call packages