Using First (I think) to write into a column (1 Viewer)

davey4444

Registered User.
Local time
Today, 09:33
Joined
Jun 16, 2013
Messages
10
Hello.

I have a db which shows the following columns -

Machine Number | Shift Date | Shift Name | Product

I want to add another column which will show "1" for the first record when each machine produces a certain product wildcard name (named "Box*") per shift (AM or PM). Therefore, it's not the first time it produces Box* on that day, but rather that shift on that day.

I've had a look at the First function in my query as well as DCount but I'm at an absolute loss.

Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:33
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Sounds like a job for subqueries or nested queries. Can you post a sample db with test data?
 

davey4444

Registered User.
Local time
Today, 09:33
Joined
Jun 16, 2013
Messages
10
Hi, thanks for your reply.
I've attached a very small set of sample data, as you can see, the records in rows 1,3-6 are all the first time that this machine has produced that type of product on that shift on that day.
The detail in row 2 is 0 because that machine has already produced that product in that shift on that day.
 

Attachments

  • Database2.zip
    16.1 KB · Views: 50

theDBguy

I’m here to help
Staff member
Local time
Today, 02:33
Joined
Oct 29, 2018
Messages
21,358
Hi, thanks for your reply.
I've attached a very small set of sample data, as you can see, the records in rows 1,3-6 are all the first time that this machine has produced that type of product on that shift on that day.
The detail in row 2 is 0 because that machine has already produced that product in that shift on that day.
Hi. Hope this works for you or maybe helps you get there.
 

Attachments

  • Database2.zip
    18.4 KB · Views: 55

davey4444

Registered User.
Local time
Today, 09:33
Joined
Jun 16, 2013
Messages
10
Hi, it seems to be working perfectly with my data so far - I'll run a few more checks but so far, so good.
I've tried to take this query further by being selective on the Product description to only perform the calculation when that Product description contains the word "Box"

Code:
Test: (SELECT Count(*) FROM T_Test SQ WHERE SQ.ID<T_Test.[ID] AND SQ.[Machine Number]=T_Test.[Machine Number] AND SQ.Product=T_Test.Product AND SQ.[Shift Name]=T_Test.[Shift Name] AND SQ.[Shift Date]=T_Test.[Shift Date])

My new code is below, but it does not appear to be returning the information I was expecting.

Code:
Test: (SELECT Count(*) FROM T_Test SQ WHERE SQ.ID<T_Test.[ID] AND SQ.[Machine Number]=T_Test.[Machine Number] AND iif(SQ.Product like "Box*",true) = iif(T_Test.Product like "Box*",true) AND SQ.[Shift Name]=T_Test.[Shift Name] AND SQ.[Shift Date]=T_Test.[Shift Date])
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:33
Joined
Oct 29, 2018
Messages
21,358
Hi, it seems to be working perfectly with my data so far - I'll run a few more checks but so far, so good.
I've tried to take this query further by being selective on the Product description to only perform the calculation when that Product description contains the word "Box"

Code:
Test: (SELECT Count(*) FROM T_Test SQ WHERE SQ.ID<T_Test.[ID] AND SQ.[Machine Number]=T_Test.[Machine Number] AND SQ.Product=T_Test.Product AND SQ.[Shift Name]=T_Test.[Shift Name] AND SQ.[Shift Date]=T_Test.[Shift Date])
My new code is below, but it does not appear to be returning the information I was expecting.

Code:
Test: (SELECT Count(*) FROM T_Test SQ WHERE SQ.ID<T_Test.[ID] AND SQ.[Machine Number]=T_Test.[Machine Number] AND iif(SQ.Product like "Box*",true) = iif(T_Test.Product like "Box*",true) AND SQ.[Shift Name]=T_Test.[Shift Name] AND SQ.[Shift Date]=T_Test.[Shift Date])
Hi. To help you figure out the "Box*" part, it might help if you could post some sample data and what result you expect to get. Thanks.
 

Users who are viewing this thread

Top Bottom