resumes123
MIS
I am using Visual Basic 6 as front end
I am using MS Access 2007 as back end
In MS Access 2007, I have 12 different databases for 12 different months ( Jan to Dec '08). Each Database is about 1.5 GB. The total size of all databases is about 18 GB
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 Visual Basic front end has the following fields
Start Date, Start time, End Date, End Time.
I am querying the databases based on this information provided at the front end.
The current query I am writing for one month 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
How could I query multiple databases for dynamically changing dates
thanks
I am using MS Access 2007 as back end
In MS Access 2007, I have 12 different databases for 12 different months ( Jan to Dec '08). Each Database is about 1.5 GB. The total size of all databases is about 18 GB
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 Visual Basic front end has the following fields
Start Date, Start time, End Date, End Time.
I am querying the databases based on this information provided at the front end.
The current query I am writing for one month 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
How could I query multiple databases for dynamically changing dates
thanks