close order when line items add up

[rainbows]

i have been asked to give a report on orders open and closed but i am struggling because if like line item 2 on the picture below if shows i have had 2 deliveries to complete the required quantity of 30

the total required is 90 items for order no 1 , yet when i try to add the total up required of 90 it now shows me 120 because it looks at the 2 records of 15 when i try to create a quiery.
is it possible to add the total required on e the form against the total checked in and put a field in that would say closed

thanks steve

 

[CJ_London]

perhaps show your query since that would appear to be where the problem lies.

Also show your table design and relationships in connection with this problem

 

[rainbows]

so i was trying not to sum up line 2 on the qty reqd

 

[CJ_London]

please provide the sql - I don't see any summing as this is a select query

Just as a tip it is very bad practice to use spaces and non alpha numeric characters in field (and table/query) names - it can cause all sorts of errors with misleading error messages

 

[rainbows]

that was not summed up
below was

 

[CJ_London]

you have been around long enough to know how to provide sql - not as an image, but as text wrapped in the code tags. Reason is the image is difficult to read and responders cannot copy/paste or reorder the text to make it readable

 

[rainbows]

SELECT [supplier P/O detail].[P/O Number], [supplier orders].[line no], [supplier P/O detail].[PCO No], [supplier P/O detail].Ordered, [booked in].[Qty Recd], [supplier orders].[qty reqr]
FROM ([supplier P/O detail] INNER JOIN [supplier orders] ON [supplier P/O detail].[P/O Number] = [supplier orders].[PO number]) LEFT JOIN [booked in] ON ([supplier orders].[line no] = [booked in].[Line no]) AND ([supplier orders].[PO number] = [booked in].[P/O No])
ORDER BY [supplier orders].[line no];

 

[rainbows]

sorry i did not do that

 

[CJ_London]

but that is not the sql you are showing in your image which has a group by

 

[CarlettoFed]

I think there are problems in the structure of the tables involved in the orders as, in my opinion, the structure should be:
tblOrders
OrderID
OrderNumber
OrderDate
SupplierID
etc...
tblOrderDetails
OrderDetailID
MaterialID
MaterialQuantity
etc...
tblMaterials
MaterialID
Material
etc...
tblSupplierSenders
SupplierSenderID
OrderDetailID
SupplierSenderDate
SupplierSenderQuantity
etc...

however, to help you you should have an example file, with a minimum of data and replacing any sensitive data, in order to correctly see all the fields of the tables as from the queries some names would seem different from those displayed in the attachments relating to the masks shown.

 

[Pat Hartman]

I hate to jump onto the complaint wagon but you aren't posting things we can work with.

The picture of the three tables joined in the query has the tables cut off. We can't see all the columns and we can't see the primary keys. We can see the joins and the joins are invalid. If you are getting wrong counts it is likely because of the join. The more tables you have in a join the more you have to be conscious of what the actual relationships are.

Although having multi-field primary keys is not wrong, it does make joins more difficult. The common solution is to use an autonumber as the PK but then create a unique index on the combination of the two fields so you can force them to be unique. ALL joins will use the PK. They will never use the two field unique index.

To make a 2-field unique index, you MUST use the indexes dialog. You can't do this directly using the table design view as you can with the PK.

 

[gemma-the-husky]

@rainbows
Can you explain your issue again. In post #3, you see the qty required adding to 120 because there are two receipts in for the second item. But the correct figures to use are those in qty received column, which show two values of 15. That total would be 90 once line 3 of the order is received.

 

[rainbows]

albeit long winded what i did it is now working the first purchase order status is for when the they have been passed by the inspector the second one is for when the stock has been changed
you can see line one has not had the tick box done if it had it would have closed the order

but very long winded to get there .