Form+Table, 2nd autonumber help

[nicksource]

I have a table that stores orders in. There is a form to input data into this table, that I choose the products from a products table (using a subform) for the order. Once I submit the form, it submits several rows according to how many products I chose (a row for each product and the order) to the orders table.

The orders table has a autonumber primary key ID, so each of these rows has a different ID number.

I also want a column to store an order number (field: OrderNo) that is the same for each row (each product) for this order in question and not be different, allowing me to do a query for that order number and see all the products under it.

So I need the form to lookup the last highest order number from the OrderNo field, add +1 then when I add a product, it gives the SAME number to each one.

I hope this makes sense and I hope you can explain to how I do this?

Thanks,
Nick.

 

[Dennisk]

it sounds as though you have not sufficiently normalised your database.

In this type of senario you require a table of Orders with at least a Primary Key (Autonumber is OK) and an Order Number. You also require an OrderLine Table this would be linked to the order table such that the Order Primary key is a foreign key in the Orderline table. That way you would have an order form and a subform linked to the order form. As you enter order lines(products) the Foreign key will be automatically inserted for you.

 

[nicksource]

Thanks Dennisk, I understand what you are saying but not sure how to implement it.

What would the form Expression be, or would it be a SQL query to get the last number from the OrderLine table? Is it possible for you to give a small example?

Many thanks,
Nick.

 

[nicksource]

Hmm, I tried doing a SQL query to select the TOP 1 of a column with the Order Number but it comes up with a ?#NAME error in the form field, works fine when running the query by its self though.

I was hoping I could select the TOP 1 with SQL then just do +1 to get the next Order Number.

Any ideas?

Thanks in advance.

 

[georgedwilkinson]

Look up the aggregate function Max().

 

[nicksource]

Still having no luck with this.

I want the next order number displayed in a text field, is it easy to use expressions/functions or will I need to do a SQL Query?