2 different autonumber inputs into same table field

[gsc_81]

Am I insane or is this possible? I have Quotes and Repairs each using a different (i.e. distinct) autonumber format which need to reside on the same table, in the same field. I tried using 2 tables which updated to a 3rd (Main) table but the formatted letter prefix (R for repairs) would not transfer in the update. Is there a more efficient way of handling this? Please be basic as I am new to Access and VB. Thanks.

 

[ecniv]

Insane


tblQuotes
tblRepairs

tblQuotesToRepairs
QtRID - autonumber - pk
RepairID - number
QuoteID - number


The Quotes to Repairs (or visa versa) holds the repair id and the corresponding quote id. Or you can do it the other way around and hold a list of quote ids with corresponding repair ids.


Vince

 

[neileg]

Why the same table? Why not use separate tables and join them with a query so all the data is available in one block when you need it, but the autonumbers can happily increment in their own tables?

 

[gsc_81]

more details

I have a fully finctioning db that works great for the quotes. My boss wants the repairs integrated into the same table. RepairID and QuoteID keys are both going to be called QuoteID and reside in the same field in the same table, so all forms, queries, reports, macros do NOT need to be duplicated and made distinct for the Repairs side. I have to keep the repairs with a distinct numbering system in the same field.

My Boss tells this is what I need.....

 

[Mile-O]

My Boss tells this is what I need.....

Repairs are not quotes and therefore do not belong in the same table. Your boss does not know what he is talking about.

 

[The_Doc_Man]

If, in your business model, these two items (quote ID and repair ID) are ever separate from each other, then in Access they have to be separate from each other. It is a normalization thing.

BTW, if your boss wants to keep the same format for quotes and repairs in forms and such, that's not a bad thing. But you CAN copy/edit forms and reports and such, then go back in and do some edits on the recordsource properties.

You can also do UNION queries to join the quotes and repairs, then drive your forms/reports from the UNION query rather than the raw tables.

 

[Pat Hartman]

Doc, a quote is the precursor to a repair so really a repair is just a status change. Hopefully the repair will be similar to the quote but some of the details might be different so in order to make accurate statistics, they need to retain their individual values. I would also keep them in the same table.

Using a single autonumber for the primary key is fine. I would add a field to indicate whether the row is for a quote or a repair. I would also add a RelatedQuoteID field. That way when you enter the repair record, you can reference the original quote if there was one. This will allow you tie the two together for analysis purposes.