Determine BatchID based on Sample Date (1 Viewer)

R

randallst

Registered User.
Local time
Today, 23:53
Joined
Jan 28, 2015
Messages
64
Hi there,

I've been trying to create a query to like the production BatchID to a Samples record based on when the samples were taken.

BatchID is when a batch of pencils have gone through a machine (i.e. what type of pencil, its Shop Order and Release No's, Start Date and End Date).

I have attached an Excel File where I have drawn out the two tables I want to link with some sample data, and I have drawn the query I want to achieve.

Any help on this matter will be greatly appreciated as im loosing it now!

Thanks in advance
Stuart :)
 

Attachments

  • Query Question.xlsx
    11.5 KB · Views: 52
R

randallst

Registered User.
Local time
Today, 23:53
Joined
Jan 28, 2015
Messages
64
I don't have any SQL as I don't have a working query.
 
P

plog

Banishment Pending
Local time
Today, 17:53
Joined
May 11, 2011
Messages
11,645
You can still have SQL. You said you tried to make a query, what was that query's SQL.

Give it a shot and then post back the SQL and what isn't working.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,230
you need to create a Form or Report
that look like the Last table on your
Excel file.

the yellow ones i think is the one you
need to get filled up with data.
you can use DLookup to get the values
from Production_Data table.

create 9 unbound textbox on this form.
add 3 labels for BatchId, Process, Product.

on the 9 unbound textbox, set their
control source.

for BatchID, the control source:
=Dlookup("BatchID","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

for Process:
=Dlookup("Process","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

for Product:
=Dlookup("Product","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")
 
R

randallst

Registered User.
Local time
Today, 23:53
Joined
Jan 28, 2015
Messages
64
arnelgp said:
you need to create a Form or Report
that look like the Last table on your
Excel file.

the yellow ones i think is the one you
need to get filled up with data.
you can use DLookup to get the values
from Production_Data table.

create 9 unbound textbox on this form.
add 3 labels for BatchId, Process, Product.

on the 9 unbound textbox, set their
control source.

for BatchID, the control source:
=Dlookup("BatchID","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

for Process:
=Dlookup("Process","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

for Product:
=Dlookup("Product","Production_Data",#" & Format([Shop Order],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")
Click to expand...

Many thanks for the detailed response, im having issues when adding the Dlookups to the Control Sources as it keeps returning "The expression you entered has an invalid date value." I did adjust the format from mm/dd/yyyy to dd/mm/yyyy but still same issue.....I'm lost now aha!
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,230
sorry, should be:

if shop order is Text (datatype)
=Dlookup("BatchID", "Production_Data", "[Shop Order]=" & Chr(34) & [Shop Order] & Chr(34) & " And #" & Format([Sample Date],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

if shop order is Number (datatype):
=Dlookup("BatchID", "Production_Data", "[Shop Order]=" & [Shop Order] & " And #" & Format([Sample Date],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

do it with the rest of the textboxes.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,230
Stick to the US date format mm/dd/yyyy
 
R

randallst

Registered User.
Local time
Today, 23:53
Joined
Jan 28, 2015
Messages
64
arnelgp said:
sorry, should be:

if shop order is Text (datatype)
=Dlookup("BatchID", "Production_Data", "[Shop Order]=" & Chr(34) & [Shop Order] & Chr(34) & " And #" & Format([Sample Date],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

if shop order is Number (datatype):
=Dlookup("BatchID", "Production_Data", "[Shop Order]=" & [Shop Order] & " And #" & Format([Sample Date],"mm/dd/yyyy") & "# Between [Start Date] And [End Date]")

do it with the rest of the textboxes.
Click to expand...

Absolutely spot on, It works perfectly! You sir are a star and have made me happy at the end of a crappy week trying to get this sorted aha!

Sorry if you got my last message where I mentioned I had issues, it was my error! :)
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,230
We all commit errors. We are human after all.
 
You must log in or register to reply here.

Similar threads

J
Single table, multiple returns on a query based on user choice
Replies
3
Views
459
GPGeorge
GPGeorge
K
Query based on multiple tables
2
Replies
21
Views
776
mike60smart
mike60smart
V
Help - Change Field Value in different table based on field selection in a form/query
2
Replies
24
Views
929
Pat Hartman
P
M
Form based on crosstab - hiding columns
Replies
9
Views
621
ebs17
E
S
Solved Pulling records based on day of the week
Replies
5
Views
290
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom