Using 1 Autonumber field for 3 tables

[SarahHall]

Hi,

I have three tables which each have their own uniqueID field that I really need to be unique across the three (for union at a later point). Therefore I have created a table which has two fields - an autonumber field and another indicating which table it lives on.

I basically want to work out an efficient way of adding a new record to the autonumber table and adding the number it produces to a new record within its equvalent foreign key.

It needs to be reasonably efficient as around 1000 records will be added at a time on one of the tables, whereas only 1 record will be added at a time on the other two.

I hope thats clear enough!

Thanks in advance
Sarah

 

[SarahHall]

If anyone has any suggestions, please let me know. I don't necessarily need the answer, just a pointer in the right direction.
Thanks

 

[stopher]

Just put the all the data in one table to start with. Use a key field to identify the difference between the three sets if you need to.

hth
Chris

 

[SarahHall]

Hmm... yes I was thinking perhaps this is the easiest solution. Thanks for your suggestion!

 

[stopher]

Hmm... yes I was thinking perhaps this is the easiest solution. Thanks for your suggestion!

It will stop you pulling your hair out.

Access will happy manage 1,000,000+ records. If you do start to hit performance issues at some point then you can look at archiving.

Good table design will help too e.g. appropriate indexing.

Chris

 

[smig]

each table should have it's oun unique keyID (Either using Autonumber or using DMax() + 1)

put another field in each table to identify the table, and merge both fields into one to create the "key" for your Union.

 

[SarahHall]

Hi Smig,
Thanks. I'd considered this too, but had decided it may be a bit fiddly/difficult, but considering the other options perhaps it seems simpler in comparison! Thanks again for your suggestion.

 

[gemma-the-husky]

Sarah

I think the real problem may be you are drifting into a faulty "spreadsheet" approach.

Taking your inital thought to have 3 tables linked by the same autonumber. If the autonumber is the only thing that identifies the common rows, then there is somethnig seriously amiss. While Autonumbers are efficient at managing links between tables, they do not reflect the underlying "real world" situation.

Either you need a single table only - or you need multiple tables. But if it is the latter, then the row identifier is not going to be the same for each table. One table will (should) act as a master, and the related table will (should) hold items related to each item in the master table.

But in the latter case, the autonumber ID of the master table item, will not be used as the ID of the sub-table - although it will be stored as a field within in the sub-table.

Can you clarify what your data contains, so that we can guide you to a better table structure?

 

[SarahHall]

Hi Dave,
The problem I am facing is that the data format is driven by spreadsheets! Information needs to be input from spreadsheets, then added to further data within the Db and then output to the same spreadsheet in the same format. Annoying as it is, I've not been involved in any of the decision making of this process and as I now can't design the Db with a sensible structure, I'm kind of stuck with making these kind of decisions.

This is activity data on call centre representatives. The purpose of this infomation is driven by two things: performance of individuals and performance of the process. The split of the spreadsheets/tables is by process, but I need to unify them to satisfy the individual performance requirement.

I'm aiming to keep the Db design as simple as I can, but I'm concerned that putting everything on the same table will cause issues with null values for some unused fields.

Sarah

 

[gemma-the-husky]

I suspected that.

but presentation and storage are different things entirely. You MUST stop thinking of the database in terms of a spreadsheet. You can store the data in the most appropriate structure, and then present it to the user in a different way.

In fact, the internal data structure should not be apparent from the functionality, I would say - so the database functions as a "black box", if you will.

BUT - a normalised data structure will be easy to work with, compared with a incorrectly normalised structure, which will continue to give you problems

You should be looking to import the spreadsheet, and then use queries etc to manipulate the data into a number of relational tables. You will almost certainly end up with several tables.

when you want to get a spreadsheet back again, prepare a query to re-join all the information together, and then output the query.

 

[SarahHall]

Thanks and yes, after thinking more about it, a few more appropriate normalisation changes will make my life easier.
Thanks again.