Question Designing queries for multiple identical table structures and expanding data

[Spence]

Hello,

I have 36 Access databases containing 2 tables each. One table contains invoice information the other the customer information. There is one Access database file (.mdb) for each month starting in 2006. The two table are identical in structure across the months. The customer table grows each month, and the information in the invoice table is (theoretically) different each month. I would like to create a single query to gather and sum all this information across all the tables. What would be the most efficient and elegant approach to access and compile this information? For example, I need to create a report showing the total revenue by branch by month by service type. I would send you examples of the tables, however, the information is considered private and confidential.

I apologise for the vagueness and lack of detail.

 

[Len Boorman]

Basically The data needs to be normalise cos it is crazy have teh table structure you have. Having said that you are probably in teh position of being stuck with what you have got

So a UNION query is the answer. But you need to be a bit cunning as well.

Union query for each year and then a UNION to union the 3 years you currently have.

Have a new field in each query purely for the year and month. You can do this with an alias.

This way you get all the data in asingle query that can be used just as if it were a single table

Alternatively import all the tables into a single application and UNION from there

or bite the bullet and create a single table. You could actually do this from the final UNION query.

Just query the UNION query with a make table query and you have all the data in a single table

Tweak the interface and stop using the other 35 applications

L

 

[MSAccessRookie]

Hello,

I have 36 Access databases containing 2 tables each. One table contains invoice information the other the customer information. There is one Access database file (.mdb) for each month starting in 2006. The two table are identical in structure across the months. The customer table grows each month, and the information in the invoice table is (theoretically) different each month. I would like to create a single query to gather and sum all this information across all the tables. What would be the most efficient and elegant approach to access and compile this information? For example, I need to create a report showing the total revenue by branch by month by service type. I would send you examples of the tables, however, the information is considered private and confidential.

I apologise for the vagueness and lack of detail.

You have 36 individual databases (as opposed to 36 separate Invoice tables in a single database)? I assume you make a new copy each month and clear out the invoice information. Without seeing the table Designs (actual values are not required), I can only say that the approach seems very inefficient and does not take advantage of some basic Access Database Design features.

Although Access does allow an application to open multiple databases, your scenario appears to create a group of databases that continues to grow on an an open ended basis that will most likely require continuous updates to any reports or code that you would write to support it.

If the Invoice information includes a Date and there is no legitimate reason to separate the data, then all of the invoice records could be in a single database table, and Forms or queries could be designed to process the required data using the date as a grouping key to provide the Monthly statistics that you are used to. Information could then be printed in reports, and even exported to Excel or other Access databases if the need arises.

The customer data may or may not be able to be handles the same way, but I suspect that there will be a great deal of overlap in the many monthly copies.

 

[Spence]

Thanks Len. I will certainly give this a go, once I have a moment. I have concerns about a single table approach as there are already 65,000 customer records at the end of 2006. In a single month there are 100,000 invoices. Do you think Access can handle it?

 

[WayPay]

There is one Access database file (.mdb) for each month starting in 2006.

Are all tables named the same each month, or is it something like Office01February2008.mdb through Office36February2008.mdb ?

If they're named the same each month, you could just link to all those tables & replace the files each month.

I would like to create a single query to gather and sum all this information across all the tables.

In the case of 36 linked tables, you could UNION them all together. I wouldn't recommend that, though. I guess it depends on the total amount of data but I suspect it would run like a dog. It's easy to test, though.

What would be the most efficient and elegant approach to access and compile this information?

Don't know about efficient and elegant, but I'd append all the data in one table and run your analysis on that. In fact, I'd have one big database where I'd keep all data for all months ever .

I know there is a way to query tables in external databases without having to create a link, but I've never used it. I think it was something like

SELECT *
FROM table
IN path_to_database

Maybe a real Access veteran would care to pipe in at this point . Otherwise, you'll have to look that up yourself.

 

[Len Boorman]

Have had 5.25 million records in a single table. No size problem but retrieval was a bit slow. needed to tweak index's.

But if you have a separete union query for each year you limit the records you are handling.

Sort of give the user a choice. last I,2 or 3 years records, then add new option each year adding a new year so next would be add last 4 years records. after 5 people would probably get bored

L

 

[Spence]

You have 36 individual databases (as opposed to 36 separate Invoice tables in a single database)?

Correct. 36 MDB files containing two tables in each; one invoice table and one customer table. I currently have a master that links to each of these .mdb files.

I assume you make a new copy each month and clear out the invoice information. Without seeing the table Designs (actual values are not required), I can only say that the approach seems very inefficient and does not take advantage of some basic Access Database Design features.

Totally, brutally, inefficient...but as Len said, we have to work with what we have been given.

Although Access does allow an application to open multiple databases, your scenario appears to create a group of databases that continues to grow on an an open ended basis that will most likely require continuous updates to any reports or code that you would write to support it.

This is correct also...I was trying not to think about.

If the Invoice information includes a Date and there is no legitimate reason to separate the data, then all of the invoice records could be in a single database table, and Forms or queries could be designed to process the required data using the date as a grouping key to provide the Monthly statistics that you are used to. Information could then be printed in reports, and even exported to Excel or other Access databases if the need arises.

The only reservation that I have is that there are 100,000 invoices per month...3,600,000 and growing. I have no idea what the limits of Access are.

The customer data may or may not be able to be handles the same way, but I suspect that there will be a great deal of overlap in the many monthly copies.

I am thinking one table because (supposedly) we never delete customer information. I could append each month reducing duplication and space.

Thanks for your help, by the way. It certainly has me thinking.

 

[MSAccessRookie]

Allow me to start over again. Your initial post did not indicate the vastness of the data that was to be considered. I suspect that it will not be too long before Access will begin to have difficulty trying to handle your situation. The volume of data is onl;y a part of the problem. The number of files creates another issue to be considered as well.

You might want to consider some type of Server Oriented Solution (Oracle, SQL Server, etc). From Experience, I can assure you that both Oracle and SQL Server have the ability to handle your needs far into the future. I am equally sure that there are others that will do just as well.

 

[Spence]

Good Gawd! Its been running for twenty minutes! Is this to be expected?

 

[MSAccessRookie]

Good Gawd! Its been running for twenty minutes! Is this to be expected?

I would not expect it to take that long. Either accessing data from multiple databases takes much longer than I am used to, or perhaps you have a Cartesian Join Scenario in progress. Take another look at your SQL code and reevaluate the Joins. Post it for us if you are able. No data required.

 

[WayPay]

Good Gawd! Its been running for twenty minutes! Is this to be expected?

If you're running a UNION query on those 36 tables:

I suspect it would run like a dog.

Yes .

If your database is on a network drive, try putting it on a local drive.

 

[Spence]

WayPay:

I tried all of your suggestions, but my computer (I guess) can't handle it!

 

[MSAccessRookie]

What have you done so far (so that we help you to try to determine the next logical step for you)?

 

[Spence]

I tried a union query between the twelve monthly tables...ended up with a non-responsive Access and a 'page-file' error. Then I tried appending each monthly table to a single table and got an 'invalid function' error...database had 2 gig. The approach I am taking now is to link to the tables, but even between only two tables a union query takes five minutes and Access shows as 'not responding'. I think the solution to my problem is going to be expensive!!!

 

[MSAccessRookie]

I tried a union query between the twelve monthly tables...ended up with a non-responsive Access and a 'page-file' error. Then I tried appending each monthly table to a single table and got an 'invalid function' error...database had 2 gig. The approach I am taking now is to link to the tables, but even between only two tables a union query takes five minutes and Access shows as 'not responding'. I think the solution to my problem is going to be expensive!!!

Is it possible to let us se the data structures involved (no data required)? Something like this from my own system is what I mean.

[B][U]tblPeople[/U][/B]
 
[FONT=Fixedsys]    Person_ID        AutoNumber[/FONT]
[FONT=Fixedsys]    Researcher       Text[/FONT]
[FONT=Fixedsys]    L_Name           Text[/FONT]
[FONT=Fixedsys]    F_Name           Text[/FONT]
[FONT=Fixedsys]    M_Initial        Text[/FONT]
[FONT=Fixedsys]    CommonName       Text[/FONT]
[FONT=Fixedsys]    Notes            Text[/FONT]
[FONT=Fixedsys]    email            Text[/FONT]
[FONT=Fixedsys]    DateAdded        Date/Time[/FONT]

 

[WayPay]

OK, the UNION was a bad idea, but that was not unexpected. As to the 2 gig database, that's an interesting problem. Looking at the amount of data you're handling, I'd suggest using another database: MSSQL, MySQL, Oracle, what have you not. Your current tool is not really up to the job. Maybe Access2007 solves some of your problems, but I can't comment on that.

Some thoughts:
1. If you're just collating the data for a report, leave out columns you're not using.
2. Have a look at your column sizes. Chopping off 100 chars from a string column really helps if you have lots of records.
3. Do the importing table by table.

Hope this helps.

 

[Spence]

I wasn't sure how inline insert, so I attached.

View attachment consDbTbl.pdf

 

[MSAccessRookie]

I wasn't sure how inline insert, so I attached.

View attachment 24444

If that is the table structures, thanks for trying, but believe it or not, I do not have a PDF Reader available here at work.

 

[bany69b]

Hello!i have a table tblcustomers and 2 fields 1*Custname 2*entry and i need to write in Custname firstfield=bany and in second field=bany just 2 time only in the third time when i try to enter bany in the field 3 i must have error so any way to do that.

 

[MSAccessRookie]

Hello!i have a table tblcustomers and 2 fields 1*Custname 2*entry and i need to write in Custname firstfield=bany and in second field=bany just 2 time only in the third time when i try to enter bany in the field 3 i must have error so any way to do that.

A couple of points:

1. This is a new Topic and as such can get much more exposure (and perhaps more and better answers) if you start a new thread, with an appropriate heading.
2. The description of the issue needcs to be expanded. Including things like better Table structure definition, and a more detailed task requirement list would be very helpful.

Please start a new thread for this