Query trouble

[StevieD via AccessMonster.com]

Hi, Im currently running the following query:
------------------------------------------------------------------------------
--------------
PARAMETERS [Start Date] DateTime, [Finish Date] DateTime, [MachineCodeA] Text
( 255 );
SELECT DISTINCTROW [SALES STATUS].Ss_WO_NO, [DESPATCH STATUS].Ds_Instance,
[SALES STATUS].[Ss_DATE_REC'D], [SALES STATUS].Ss_QUOTE_NO, [SALES STATUS].
Ss_CUSTOMER_NAME, [SALES STATUS].Ss_CODE, [SALES STATUS].Ss_CUST_ORD_NO,
[SALES STATUS].[Ss_PART_NO/DRWG], [SALES STATUS].Ss_ISSUE_NO, [SALES STATUS].
Ss_QTY, [SALES STATUS].Ss_PRICE_PER_100, [DESPATCH STATUS].
[Ds_DATE_Ship_Req'd], [DESPATCH STATUS].Ds_WK_ACK, [DESPATCH STATUS].
[Ds_QTY_REQ'D], [DESPATCH STATUS].[Ds_DATE_REQ'D], [DESPATCH STATUS].
[Ds_WK_REQ'D], [DESPATCH STATUS].Ds_DESPATCH_QTY, [DESPATCH STATUS].
Ds_DESPATCH_DATE, [Ds_QTY_REQ'D]*[Ss_PRICE_PER_100]/100 AS Expr1, [DESPATCH
STATUS].Ds_DATE_ACK, [DESPATCH STATUS].Ds_Ship_Comment, MachineCode.
MachineCode AS MCA, MachineCode_1.MachineCode AS MCB, MachineCode_2.
MachineCode AS MCC, Status.Status AS SA, Status_1.Status AS SB, Status_2.
Status AS SC
FROM [SALES STATUS] INNER JOIN (((((([DESPATCH STATUS] LEFT JOIN MachineCode
ON [DESPATCH STATUS].MachineCodeA = MachineCode.ID) LEFT JOIN MachineCode AS
MachineCode_1 ON [DESPATCH STATUS].MachineCodeB = MachineCode_1.ID) LEFT JOIN
MachineCode AS MachineCode_2 ON [DESPATCH STATUS].MachineCodeC =
MachineCode_2.ID) LEFT JOIN Status ON [DESPATCH STATUS].StatusA = Status.ID)
LEFT JOIN Status AS Status_1 ON [DESPATCH STATUS].StatusB = Status_1.ID) LEFT
JOIN Status AS Status_2 ON [DESPATCH STATUS].StatusC = Status_2.ID) ON [SALES
STATUS].Ss_WO_NO = [DESPATCH STATUS].Ds_WO_NO
WHERE ((([DESPATCH STATUS].[Ds_DATE_Ship_Req'd]) Between [Start Date] And
[Finish Date]) AND ([MachineCode].[MachineCode])=MachineCodeA AND ((
[DESPATCH STATUS].[Ds_QTY_REQ'D]) Is Not Null) AND (([DESPATCH STATUS].
Ds_DESPATCH_QTY)=0));
------------------------------------------------------------------------------
--------------

Bascially this script makes the user select a 'Start Date', 'End Date' and
'MachineCodeA' I also have a MachineCodeB and MachineCodeC field.

I would like the user to be able to simply enter the Start date, End date,
and then MachineCode and for it to look for what is entered in the
MachineCodeA,B or C fields.?

Any ideas?

 

[OfficeDev18 via AccessMonster.com]

Instead of running this as a standalone query, I would put it on a form. Make
the three 'criterion' fields as combo boxes, and then open the appropriate
tables only, and look for available MachineCodeX information, which can be
returned via another combo box's Row Source. In other words, make the fourth
combo box's (or multiple ones, if it's easier when code a, b, and c are
separated) row source be the SELECT statement that returns only the available
CodeXes, based on the above criteria, for the user to choose. This
information can then be used to locate the unique record(s) that meet all the
criteria.

HTH

Hi, Im currently running the following query:
------------------------------------------------------------------------------
--------------
PARAMETERS [Start Date] DateTime, [Finish Date] DateTime, [MachineCodeA] Text
( 255 );
SELECT DISTINCTROW [SALES STATUS].Ss_WO_NO, [DESPATCH STATUS].Ds_Instance,
[SALES STATUS].[Ss_DATE_REC'D], [SALES STATUS].Ss_QUOTE_NO, [SALES STATUS].
Ss_CUSTOMER_NAME, [SALES STATUS].Ss_CODE, [SALES STATUS].Ss_CUST_ORD_NO,
[SALES STATUS].[Ss_PART_NO/DRWG], [SALES STATUS].Ss_ISSUE_NO, [SALES STATUS].
Ss_QTY, [SALES STATUS].Ss_PRICE_PER_100, [DESPATCH STATUS].
[Ds_DATE_Ship_Req'd], [DESPATCH STATUS].Ds_WK_ACK, [DESPATCH STATUS].
[Ds_QTY_REQ'D], [DESPATCH STATUS].[Ds_DATE_REQ'D], [DESPATCH STATUS].
[Ds_WK_REQ'D], [DESPATCH STATUS].Ds_DESPATCH_QTY, [DESPATCH STATUS].
Ds_DESPATCH_DATE, [Ds_QTY_REQ'D]*[Ss_PRICE_PER_100]/100 AS Expr1, [DESPATCH
STATUS].Ds_DATE_ACK, [DESPATCH STATUS].Ds_Ship_Comment, MachineCode.
MachineCode AS MCA, MachineCode_1.MachineCode AS MCB, MachineCode_2.
MachineCode AS MCC, Status.Status AS SA, Status_1.Status AS SB, Status_2.
Status AS SC
FROM [SALES STATUS] INNER JOIN (((((([DESPATCH STATUS] LEFT JOIN MachineCode
ON [DESPATCH STATUS].MachineCodeA = MachineCode.ID) LEFT JOIN MachineCode AS
MachineCode_1 ON [DESPATCH STATUS].MachineCodeB = MachineCode_1.ID) LEFT JOIN
MachineCode AS MachineCode_2 ON [DESPATCH STATUS].MachineCodeC =
MachineCode_2.ID) LEFT JOIN Status ON [DESPATCH STATUS].StatusA = Status.ID)
LEFT JOIN Status AS Status_1 ON [DESPATCH STATUS].StatusB = Status_1.ID) LEFT
JOIN Status AS Status_2 ON [DESPATCH STATUS].StatusC = Status_2.ID) ON [SALES
STATUS].Ss_WO_NO = [DESPATCH STATUS].Ds_WO_NO
WHERE ((([DESPATCH STATUS].[Ds_DATE_Ship_Req'd]) Between [Start Date] And
[Finish Date]) AND ([MachineCode].[MachineCode])=MachineCodeA AND ((
[DESPATCH STATUS].[Ds_QTY_REQ'D]) Is Not Null) AND (([DESPATCH STATUS].
Ds_DESPATCH_QTY)=0));
------------------------------------------------------------------------------
--------------

Bascially this script makes the user select a 'Start Date', 'End Date' and
'MachineCodeA' I also have a MachineCodeB and MachineCodeC field.

I would like the user to be able to simply enter the Start date, End date,
and then MachineCode and for it to look for what is entered in the
MachineCodeA,B or C fields.?

Any ideas?

 

[Guest]

The basic problem are the MachineCodeA, MachineCodeB, and MachineCodeC
fields. They should not be in the table. What happens to your queries,
forms, reports, and code is someone decides you need a MachineCodeD?

Rather you should have a MachineCode table linked to the primary key in the
parent DESPATCH STATUS table. This table should also have a primary key like
an autonumber just to keep track of records. Then you could have a
MachineCode field and a Status field for the MachineCode.

This way you avoid the convoluted self-joins and lef joins; can just ask the
question to one field instead of three; and easily add or subtract
MachineCodes as necessary.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi, Im currently running the following query:
------------------------------------------------------------------------------
--------------
PARAMETERS [Start Date] DateTime, [Finish Date] DateTime, [MachineCodeA] Text
( 255 );
SELECT DISTINCTROW [SALES STATUS].Ss_WO_NO, [DESPATCH STATUS].Ds_Instance,
[SALES STATUS].[Ss_DATE_REC'D], [SALES STATUS].Ss_QUOTE_NO, [SALES STATUS].
Ss_CUSTOMER_NAME, [SALES STATUS].Ss_CODE, [SALES STATUS].Ss_CUST_ORD_NO,
[SALES STATUS].[Ss_PART_NO/DRWG], [SALES STATUS].Ss_ISSUE_NO, [SALES STATUS].
Ss_QTY, [SALES STATUS].Ss_PRICE_PER_100, [DESPATCH STATUS].
[Ds_DATE_Ship_Req'd], [DESPATCH STATUS].Ds_WK_ACK, [DESPATCH STATUS].
[Ds_QTY_REQ'D], [DESPATCH STATUS].[Ds_DATE_REQ'D], [DESPATCH STATUS].
[Ds_WK_REQ'D], [DESPATCH STATUS].Ds_DESPATCH_QTY, [DESPATCH STATUS].
Ds_DESPATCH_DATE, [Ds_QTY_REQ'D]*[Ss_PRICE_PER_100]/100 AS Expr1, [DESPATCH
STATUS].Ds_DATE_ACK, [DESPATCH STATUS].Ds_Ship_Comment, MachineCode.
MachineCode AS MCA, MachineCode_1.MachineCode AS MCB, MachineCode_2.
MachineCode AS MCC, Status.Status AS SA, Status_1.Status AS SB, Status_2.
Status AS SC
FROM [SALES STATUS] INNER JOIN (((((([DESPATCH STATUS] LEFT JOIN MachineCode
ON [DESPATCH STATUS].MachineCodeA = MachineCode.ID) LEFT JOIN MachineCode AS
MachineCode_1 ON [DESPATCH STATUS].MachineCodeB = MachineCode_1.ID) LEFT JOIN
MachineCode AS MachineCode_2 ON [DESPATCH STATUS].MachineCodeC =
MachineCode_2.ID) LEFT JOIN Status ON [DESPATCH STATUS].StatusA = Status.ID)
LEFT JOIN Status AS Status_1 ON [DESPATCH STATUS].StatusB = Status_1.ID) LEFT
JOIN Status AS Status_2 ON [DESPATCH STATUS].StatusC = Status_2.ID) ON [SALES
STATUS].Ss_WO_NO = [DESPATCH STATUS].Ds_WO_NO
WHERE ((([DESPATCH STATUS].[Ds_DATE_Ship_Req'd]) Between [Start Date] And
[Finish Date]) AND ([MachineCode].[MachineCode])=MachineCodeA AND ((
[DESPATCH STATUS].[Ds_QTY_REQ'D]) Is Not Null) AND (([DESPATCH STATUS].
Ds_DESPATCH_QTY)=0));
------------------------------------------------------------------------------
--------------

Bascially this script makes the user select a 'Start Date', 'End Date' and
'MachineCodeA' I also have a MachineCodeB and MachineCodeC field.

I would like the user to be able to simply enter the Start date, End date,
and then MachineCode and for it to look for what is entered in the
MachineCodeA,B or C fields.?

Any ideas?