db Design

[awm]

I'm really stumped on a db that I think should be so simple...

I want to convert a 5 field Excel spreadsheet (with 1000 rows) to Access.
The table consists of the following fields...

1. Sequential number (1 to 1000)
2. Customer
3. Date
4. Comment Field
5. Quote No (= "QT-"&"-"&Sequential Number &"-"&Customer&"-"&Date)

My assumptions for the new db are...

1. I need to create an AutoNumber field in the db (for replication) since I
would like to create an access db that is has 3 users on our server. I
assume it will be split into a front-end, back-end.
2. I will need to create a query or other method that finds the next
sequential number to continues the quote number logic when convert to an
Access db.

As you can probably determine, the table creates a new quote number...
that's it!... so simple, right?

Well here's the problem... under Access db design mentioned, how can I
assure that a user will obtain the next sequential number without
duplication? In other words, if two users were to create a quote number at
the same time, how can I be assured that both will not obtain the same
sequential number in they quote number?

If this is in fact a problem, I'd appreciate any advice to have a more
appropriate db design.

Thanks!!!




The

 

[Allen Browne]

To answer your question, it might be simplest to:
1. Create a table with an AutoNumber field.

2. Attach the Excel spreadsheet: File | Get External | Link.

3. Create a query into the attached spreadsheet "table", change it to an
Append query (Append on query menu), and so append the data to the table.

The AutoNumber will then pick up from the last assigned number, and work as
it normally does. (Note that Access skips some numbers, - e.g. if you start
entering a quote and then abort the entry - but it solves the duplicates
problem.)

As far as the design goes, I would imagine you would need these tables as a
minimum:
Client table:
ClientID primary key
ClientName
etc.

Quote table (one record for each quotation given to a client):
QuoteID primary key
ClientID foreign key to Client.ClientID. Who the quote is
for.
QuoteDate Date/Time When the quote was given.
EmployeeID who gave this quote.
Comment etc.

QuoteDetail table (one record for each line item on a quote):
QuoteDetailID primary key
QuoteID foreign key to Quote.QuoteID
ItemID an item included in this quote.
Amount the amount quoted for this item.