How do i get a total from subform on purchase order?

[Sandretto]

I have created a database to create and store purchase orders. My tables are as follows -
Tbl Order details (Qty, desc,price,total),
Tbl Order no (ON, date, date required, supplier)
Tbl Supplier (Supplier name, address)

I have created the Purchase order in form with subform. I am having difficulty getting the form to create a final total. I have tried '=sum([total]).

[mshmyob]

You are going to have a lot more trouble than trying to do a simple addition.

1. You need first to not use reserevd words such as ON and Date.

2. You need primary keys and foreign keys (you have no relations between your tables.

3. You need to normalize your database.

I would suggest reading up on normalization.

cheers,

[Sandretto]

Thanks for your help I'll have a read..I do have relationships attached screen dump, showing tables/relationships.

[NeoPa]

Sandretto, Part of your task when posting your question is to consider what information is required to enable someone to understand it well enough to give an answer.

In this case (all) the details of the table structure are required, as well as enough information about your form to indicate what might be going wrong. It's hard to answer a question when there is no real question there.

[Sandretto]

Apologies for the lack of information. I have attached a screen print from design view of form.
I need to add up a grand total from the 'Total' column of the subform. On the screen print you can see the attempt I made at the sum, but this doesn't work.

SQL for Main form -

SELECT [ORDER NO].[TO:], [ORDER NO].[ORDER NO], [ORDER NO].[ORDER DATE], [ORDER NO].[DELIVERY ADDRESS], [ORDER NO].[REQUIRED DATE], [DELIVERY ADDRESS].NAME, [DELIVERY ADDRESS].[ADDRESS 1], [DELIVERY ADDRESS].[ADDRESS 2] AS [DELIVERY ADDRESS_ADDRESS 2], [DELIVERY ADDRESS].[ADDRESS 3] AS [DELIVERY ADDRESS_ADDRESS 3], [DELIVERY ADDRESS].POSTCODE, SUPPLIER.[SUPPLIER NAME], SUPPLIER.ADDRESS1, SUPPLIER.[ADDRESS 2] AS [SUPPLIER_ADDRESS 2], SUPPLIER.[ADDRESS 3] AS [SUPPLIER_ADDRESS 3], SUPPLIER.[POST CODE]
FROM SUPPLIER INNER JOIN ([DELIVERY ADDRESS] INNER JOIN [ORDER NO] ON [DELIVERY ADDRESS].[DEL ADD CODE] = [ORDER NO].[DELIVERY ADDRESS]) ON SUPPLIER.[SUPPLIER CODE] = [ORDER NO].[TO:];

SQL from subform -

SELECT [ORDER DETAILS].QUANTITY, [ORDER DETAILS].DESCRIPTION, [ORDER DETAILS].PRICE, [QUANTITY]*[PRICE] AS TOTAL
FROM [ORDER DETAILS];

Sorry again for my mistake. I hope I have given the full question and information required

[NeoPa]

Sandretto:
On the screen print you can see the attempt I made at the sum, but this doesn't work.

I'm afraid we cannot see it. It seems you've done a much better job now certainly. All text should be posted as text though. Pictures are not of good enough quality generally for passing textual information. We would also need an explanation of what does happen if, as you say, it doesn't work. Not working can mean so many things.

I suggest you also respond to Mshmyob's post directly. He has offered some important advice.

[Sandretto]

Morning
I'm not very good at this forum thing! I am still learning tho. I have good news I have worked out how to do what I requested. I 'normalised' my database from the advice give by mshmyob. I then thought about what I wanted. I entered "=sum([Quantity])*([Price]") into the form footer on the subform, this calculated the total and then on the main form entered "=(order details subform)!Grand Total" All is working order thanks for everyones help and sorry for the confusion on my part.

[NeoPa]

Sandretto:
I'm not very good at this forum thing! I am still learning tho.

That's the important thing :-) No apologies necessary. You did a fine job considering it's all very new for you.

Good job on working out the solution as well ;-)