Calculating Field Value

[jamescullis]

Hi all, I need help with calculating a field value please.

I've got 5 products, numbered 1 - 5 in tblProducts.productID
In one order I'll only ever have the 1 - 5 products, with a maximum of 5 products in any Order.

products
1 = mushroom
2 = tomato
3 = egg
4 = fish
5 = meat

tblOrder.orderID
tblOrderDetail.productID
I have a field in tblOrderDetail called "productNumber" which is where the result should go of the calculation.

I need to calculate the total number of products ordered but it could be a combination of them, the thing is that I need to reference them in a specific order if they exist in the order as per the schedule above.

example
order 1 = products 1,3,4

the result should be
1.3 (mushroom)
2.3 (egg)
3.3 (fish)

example 2
order 2 = products 1,4

the result should be
1.2 (mushroom)
2.2 (fish)


example 3
order 3 = products 4,1 (this one shows that we haven't ordered it in order, I still need to display that correct order)

the result should be
1.2 (mushroom)
2.2 (fish)

any mathematicians out there?

* also may be the wrong forum sorry

 

[CJ_London]

looks like you have three things you want:

1. a calculated row number
2. a count of the total number of products by order
3. the results to be ordered by productID

this query should give you the above per colour

SELECT OrderID, 
         [COLOR=red](select count(*) from tblOrderDetail as T WHERE OrderID=tblOrderDetail.OrderID AND productID<=tblOrderDetail.productID) AS RowNumber[/COLOR],
         [COLOR=seagreen](select count(*) from tblOrderDetail as T WHERE OrderID=tblOrderDetail.OrderID) AS TotalProducts
[/COLOR] FROM tblOrderDetail
[COLOR=orange] ORDER BY productID
[/COLOR]

you haven't explained where your product description is so I have not included that.

 

[CJ_London]

I have a field in tblOrderDetail called "productNumber" which is where the result should go of the calculation.

Forgot to say - you should not store calculated values in tables but calculate them as and when required

 

[jamescullis]

awesome CJ, on the right track now thanks

 

[jamescullis]

CJ,

I've got one more step and that to join the result of RowNumber & TotalProducts into the productNumber we need.

RowNumber | TotalProducts | productNumber
1 | 3 | 1.3
2 | 3 | 2.3
3 | 3 | 3.3
1 | 1 | 1.1
1 | 1 | 1.1

thanks for your help!

 

[jamescullis]

ignore the last post, thanks again

 

[jamescullis]

Hi CJ,
hope you can advise, I've added another field which is "optional". The field name is colourID. My problem is that it now returns "0.0" if I use the below code.

I've been looking about but can't find the terminology to replace the additional AND, I guess I'm looking for something like AND/OR if you know what I mean?

SELECT OrderID, 
         (select count(*) from tblOrderDetail as T WHERE OrderID=tblOrderDetail.OrderID [COLOR="Red"]AND colourID=tblOrderDetail.colourID[/COLOR] AND productID<=tblOrderDetail.productID) AS RowNumber,
         (select count(*) from tblOrderDetail as T WHERE OrderID=tblOrderDetail.OrderID [COLOR="red"]AND colourID=tblOrderDetail.colourID[/COLOR]) AS TotalProducts
 FROM tblOrderDetail
 ORDER BY productID

 

[CJ_London]

not sure where the colourID is coming from and struggle to relate it to fish etc. But for the first calc since an order row has only one colour it will never find another row for the same order and colour with an id less than the current id.

It seems to me you have simplified your data to get an answer and now the answer doesn't work because your data is actually more complex. Certainly I do not understand what your data is now.

Suggest you post your real data or fields to provide a proper background so a proper answer can be provided

 

[jamescullis]

hi cj

Didn't spell it out very well then, sorry.

I'll prepare one tomorrow.

 

[jamescullis]

Hi CJ,

I've uploaded the mockup for you. View attachment testorderMulti.accdb

there are 2 query's

- qryMultiCard_working
just the basic one which gives me the result you provided - I added one more called Multicard that displays the data as i need it

- qryMultiCard_notworking
in this query, I added the "AND colourID=tblOrderDetail.colourID" which is not the result I'm looking for. "colourID" is an optional field as you'll see.

What I'm looking to achieve is the same result as in "qryMultiCard_working" however if I add the same productID for the same OrderID but include a colour then it returns the incorrect results as you see.

 

[CJ_London]

the colourid column is only partially populated which is why you are having a problem - orders 2 and five have no colourid value for example.

I am unable to work out what it is you want so suggest to be clear, take your tblOrderDetail and copy into Excel

Then 'mock up' the result you want in Excel and post this as a screenshot (copy and pasting the values does not work)

It would also be helpful if you described the business process around this - why you need it, why there can be no values etc. With the example provided, it may be that the correct answer is to move the colourID field from order details to order

 

[jamescullis]

Hi CJ, I modified the the names so it may make more sense.

lets say its a cleaning business where your engaged to clean one or more parts of a house as a "project": floors, windows, doors etc.

In some cases on the same "project" you have units, each unit has floors, windows, doors to clean.

the result I need is to number the unit jobs separately (Multicard), mockup screenshot below as requested.


As you'll see, "project" 1, 3, 6 are the same address, but have units which you need to clean, products are duplicated across each unit. "project" 2, 5 are houses but have one product for each.

I've also attached the revised DB for you to refer to.
View attachment testorderMulti.accdb

many thanks

 

[CJ_London]

this is the full query since I also had to make some changes to the rest of it

 SELECT tblProjectDetail.projectID_FK, tblProject.projectName, tblProject.projectAddress, tblProjectDetail.unitNumber, tblProduct.productName, (select count(*) from tblProjectDetail as T WHERE (ProjectID_FK=tblProjectDetail.ProjectID_FK and nz(unitnumber)=nz(tblprojectdetail.unitnumber)) AND projectDetailID<=tblProjectDetail.projectDetailID) AS RowNumber, (select count(*) from tblProjectDetail as T WHERE ProjectID_FK=tblProjectDetail.ProjectID_FK AND nz(unitnumber)=nz(tblprojectdetail.unitnumber)) AS TotalProducts, [RowNumber] & "." & [TotalProducts] AS MultiCard
FROM tblProduct INNER JOIN (tblProject INNER JOIN tblProjectDetail ON tblProject.projectID = tblProjectDetail.projectID_FK) ON tblProduct.productID = tblProjectDetail.productID_FK
ORDER BY tblProjectDetail.projectID_FK, tblProjectDetail.unitNumber, tblProjectDetail.projectDetailID;

Once you've pasted into the sql window, go back to the query builder to see the changes.

You had the principle right, but were working of a wrong field for part of the calculation - you should have been using

projectDetailID<=tblProjectDetail.projectDetailID

In addition, because unitnumber is not always completed, you need to use the nz function

nz(unitnumber)=nz(tblprojectdetail.unitnumber)

 

[CJ_London]

forgot to mention the other changes - because the subquery works off projectDetailID you need to order by it to get it to display properly

 

[jamescullis]

mate, you nailed it!

thank you so much