Special auto numbering...

Hi all.

It is my first post here since I started learning Access from the Access Bible book. Putting it simple: I’m an absolute beginner.

I’m building a database to help me in my work. We sell three basic types of references and we have classified them by three letters: L, S and U. A client (with is data stored in a table) makes an order (6 digit number and related data stored in a second table) and that order includes one or more of those three products. But they must be numbered sequentially and that is what I cannot define :(

Example:
Order 010234, 2 references:
-L0124
-L0125

Order 024568, 3 references:
-U0023
-S1253
-L0126 <- this one must be sequential when I choose "L" in the type of ref on the appropriate field.

Next order:
-U0024
-S1254
-L0127

The data for each reference is stored in a third table. So, is it possible to have a key field in this third table auto numbered according to the sequence? Or am I defining the whole thing wrong?

Thx all.

by the way... does anyone uses a Pocket PC? I'm trying to implement my DB and to synch it with my PPC...

Cyas.

Define three table, one for each reference with an autonumber code.
Otherwise you can define a single table with an autonumber fields but in this case you cannot have (I mean without coding) same number for different letters.
In effect there is a logic to calc the code.

Saluto. Thank you for your reply Berto.

Anyway, I was a bit scared someone suggested your solution :P

Like I said I'm just a newbie and my database is GROWING a lot... I'll try the three new tables but I'm allready trying to figure how am I going to relate them with all the other tables :) All's a big mess in my mind right now hehe.

Grazie Berto

From what I can understand from your question, you want the references that start with a 'L' to be in sequential order? Well, you can just make sure your query is sorting in ascending order using that ID. It will pop up in sequential order.

I hope this helped.

Greetings.
Following my last questions I’m trying to define three product tables in my DB. I made the relationships between them but they don’t seem to work.

I need to achieve this:

Clients table: defined by ClienteID.
This client makes an order now and maybe others in the future.
Orders table: order defined by NOrcamento
This order contains one or more products.
The products are of L, D or U type and must be numbered sequentially in three different tables. Their ID must be unique.

By now I cannot attribute two different products to one order… I think this will involve sub forms and that is still way out of my range…

I’m including my bug crowded database :) Can someone give me a hand?

Thx all in advance.

to any of those people that has downloaded my file... can some of you point me some directions by now? :(

Thx all. Cyas

You need to have an order table that would typically include fields like order number, date of order, customer, etc. etc. You would then need to have an order details table that would typically include fields like product, quantity, etc. etc. The order details table would have a 1 to many relationship with the orders table i.e. order table - 1 order number, order details table many products for the one order.

I have checked your database but cannot understand the language so I cannot check them out for you. If you tell me what the fields and tables are in English I will check them for you.

Hi brju.

I've changed the names to English so I thing they are a lot easier to understand... sorry for my lapse.

About the tips you gave me, I think I've managed to achieve something alike but something’s still missing... don't know what or how to proceed...

Thanks a lot for your time. I'll be anxiouly waiting for your reply.

Cya

Please see attached copy of your database...

I have added a few forms with comments (refer to comments on forms for explanation) to illustrate form / subform functionality. I have also fixed the relationship joins between your order table and your two product tables as they were incorrect. Note: I had to change your seller field in the order table to enable me to enter some info - you did not include the table this field referred to.

The order detail form with the two subforms will work as is, however I do not feel that you have an optimised table structure. Ideally you should have one products master table with all product info captured. You should then have an order table with an example of the info as mentioned before. Linking to the orders table you should have an order details table with an example of the info as mentioned before. With this structure you will be able to base queries and reports on these tables without any issues occurring (See your query - "EscadasList" and note the duplicate records in the product related fields.

I do not understand the neccesity of having a unique identifier each time you select a product, from your product table, when ordered? Ideally your unique identifier, per item ordered, would be an order details id.

I suspect that the problems you are experiencing are due to the current table structure.