database setup question

[buddyel]

My company uses an Access database to keep track of customer orders. The database is setup so every customer has its own table. I would like to redesign this database to make it more relational but I am not sure on how to accomplish what i want to do. I would like to set up a table for each year of orders (Orders2000, Orders2001, etc) along with a customer information table. I have no problem creating the tables but I am not sure how I would create a query that prompts for a customer number and load all of the customer orders when they are in multiple tables. Any ideas are greatly appreciated. Thanks.

 

[WildHare]

Well, one way of doing this is through a small procedure that loops through each of the tables in the database, running an APPEND query using each one, appending to your new Operational table.

Unless you've only got half a dozen or so customers, I wouldn't bother with a prompting query. PITA

Check out the Tables collection and it's properties. I haven't used this guy recently but basically, you want to do something like this:

For each Table in MyDatabase

if Table is a Customer Table ( ??? )

RunSQLCodeToAppendToMyNewTable

else
next Table in MyDatabase

One way to differentiate the customer tables from the others is to PREFACE the names of the OTHER tables (I'm assuming there will be fewer of them) with Usys or Msys - which you can then select against in your collection-browsing.




Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[buddyel]

I think you answered another question I had but didn't ask yet and that was how to load the old data into the new table structures. What I need to know is after I setup the new table structures and have the data loaded into the proper tables how do i setup a query to load all orders for a specific customer when the orders are spread across multiple tables...

tables
-----
CustMaster
Orders1998
Orders1999
Orders2000
Orders2001
Orders2002
Orders2003

Fields in the Orders' tables.
CustNo
WorkOrder
CustPO
GEHPN
CustPN
Watts
Volts
Description

I would like a query to show only these columns but with data from ALL of the Orders tables. Can this be done?

 

[WildHare]

Not easily. The problem is your duplication of Orders through the years. I'm not so sure this is the best way to do it, although it may seem that way at first glance.

It would be more normalized if you simply had a field in your ORDERS table for DATE of order. It would be non-unique, of course, esp. if a customer could place more than one order on a day. If that were not the case, you would key the Orders table on Custno/OrderDate, and this one table would be the MANY side of a ONE to MANY with Customers.

It's very simple using the built in Date functions to pull out orders for any one year (e.g., Year(OrderDate) = "2002&quot for any one or multiple customers.

It would be a pretty simple job then to populate this table with your various yearly guys.

Jim




Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[buddyel]

I forgot to include the OrderDate column in my last posting. Also, our WorkOrder number is never duplicated. I am not sure the way to redesign the database, i just know that the current structure does not allow for any kind of consistency. I also thinking creating two tables (CustMaster and Orders) wouldnt work either, so that is why I came up with the structure I have listed above..

 

[WildHare]

I also thinking creating two tables (CustMaster and Orders) wouldnt work either,

Why is that? A Customer-Order-OrderDetails structure is very common (e.g., Northwind)

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[buddyel]

I just thought having all the records in one table might slow the database down. Do you know what the max # of records for table in Access 2002?

 

[WildHare]

There is no practical maximum number of records, per se. An A2K2 MDB file can be no more than 2GB in size.

I would not worry about making design compromises to improve performance until all other avenues of enhancement have been exhausted. Have people complained that the database is "too slow" ??

When you start getting three or four million customer/order details, we can think about possible performance issues. Until then, it's far more important to design the beast correctly.

You'd be suprised, in fact, how much CORRECT DESIGN feeds into the performance mix in the first place.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm