Refreshing a Database

[TastyWheat]

This is a lot more complicated than the title suggests. I was asked to reset the work order numbers used by the maintenance department. When they reach work order #9999 they want to start over. My best bet is to make a backup of the tables and create new ones that include the last 2-3 weeks of activity. If I do that I'll have two potential problems:

• The next autonumber needs to be lower than the pre-existing records but not start at 1
• Recombining data from the old table (for whatever reason) will create false relations

Does anyone know a possible solution to these problems or is there a better method to refresh the database?

 

[Banana]

Autonumber should not have any meaning at all. If you are using it, it won't work as you may expect.

Easier to add an field, call it OrderID, make it a integer data type then you can program it to loop through 1 to 9999 while keeping the autonumber as an unique identifier.

 

[TastyWheat]

Hmm, with that in mind do you think it would work well if the number kept repeating (1 to 9999) and when the number comes back around old records would be overwritten? Backups will still be made of course. Each record has a date field, so Date + ID = Primary Key 4 Life.

 

[Banana]

No No No.

You should only create relationship with Autonumber ID only. That's IT. Composite keys only bloat up the database and makes everything slower.

You create joins (which isn't same thing) with the OrderID, Date or whatever you need to pull together.

If you base your forms on queries using joins, this will work much better and require less maintence.

And to answer your question, it won't be overwritten because the autonumber is used as a unique primary key. OrderID can repeat itself and even better, with a query, you can easily differniate between several loops as you like.

HTH.

 

[boblarson]

I beg to differ (slightly) - composite keys are not necessarily to be avoided at all costs, but they should only be used when NECESSARY to ensure uniqueness at the record level. There are times where a composite key is going to be totally correct to use.

 

[TastyWheat]

Whew, once again I have to try and get on board with what you're saying. Intuitively I would've guessed it's better to create a composite key with what you've got rather than add an extra field for a primary key. So are you saying I should use some unique number (from 1 to infinity) apart from the order ID?

If so I can kind of see where you're going with that. A "behind the scenes" ID can be used to identify each work order and it can be used in every relationship. When tasks and purchases are added I'll lookup the most recent order with the same ID (using code of course) to get to the primary key of the order.

Am I close?

 

[Banana]

TastyWheat- Yes, that's what I was thinking. You got it all right. Don't forget that you can use joins to make this all efficient.

Bob, you're right; there are uses for composite key- Would you say Tastywheat's situation is one where a composite key would be better than an autonumber?

 

[TastyWheat]

I'm actually inclined to agree with you on this one Banana. Without a primary key my other tables will have to use two foreign keys (orderID,date) to get the right work order. Plus, if I use the primary key the other tables can store that instead of the orderID (which I could find out thru lookup).

And when you talk about using joins, something I'm slightly familiar with, are you saying that my control source for my form can use a join query instead of a single table?

 

[Banana]

Yes.

You can base your forms' record source on a query. Queries are where you do the joins. You create a join by dragging a field from one table to another (just like you do with relationship in relationship window), specify how you want to join (left, right or inner). This will revise the query's result.

Play with it a bit; you'll learn more this way than having me trying to explain it in 1,000 page essay. If you have a specific question, come back and we'll try and explain it in greater details.