Just a tip on how to begin

[XelreD]

Well guys, I know almost nothing of Access but I've read and tried a few things with tables and Access Bible.

I asking for a tip on how to begin a relational db so i'll explain you my scenario.

I have to register 3 types of products that we can build specifically for each costumer. No products are alike but fall in three major groups A, B or C. Each type of product is numbered from 0 to 9999. So a costumer order can be made of products A0020, B0100 and C0250. Example:

***********************
Order: 235495
Client name: John Doe
Produtcs:
- A0020
- B0100
- C0250

***********************
Order: 235501
Client name: Richard Blue
Products:
- B0101

***********************
Order: 235502
Client name: George MC.
Products:
- A0021

***********************
Each product as its diferent specifications as height, measure, weight...

This calls for three different tables (Atype,Btype,Ctype) and a fourth one (orders) to relate all three. I wanted to automatically number each new product each time a new order was filled. If i sell a new A product is reference would be A0022. How can I do this automatically?

If anyone can indicate me just a direction on how to proceed I wold be most grateful. Thx.

[KnooKie]

have a look at the AutoNumber datatype for auto numbering

[SQL_Sam]

This sounds like a basic order entry database scenario.

Basically you'll want:
1 product table. have Product ID (primary key), product name, description, specifications, also a 'type' field (for different product types - ie A, B, C)

2) Orders table (Order ID (primary key) (for tracking purposes), date (for date of order), Quantity (in case ordering more then one of the same product) the product id (the product being ordered), and a field for customer ID .

3) Client/Customer table for who is making the order (Client ID (primary key), name, address to ship etc)


create a many to one relationship between the Order and Product table. (you can have many orders to a product)

create a one to many relationship between the order and client table (a customer/client can have many orders)


I would use the autonumber for most of the ID fields that you can as it becomes a headache to create you're own unique idenitiers. If you dont have to many products you can go right ahead and use your alphanumeric ID, but if you have to key in say over a hundred products I would just go with the autonumber as most people are looking at the product name / description when they order something they could really care less about what the products ID is.

Hope this helps.

[XelreD]

Thank you for your replies.

About my personal identifiers; that's one thing that workers in the factory are used to. Since our products are unique they are used to identify them across the production flow by "A101" or "B057" because we found it it would be easier to know what product we where talking about, A, B, C and so on.

Now I'm thinking I can always create a report for each type of product (table) and fixed label "A:" and then just show the autonumber from the Id field... my problem is when quering from a SQL search...

Thx again.