Queries using tables w/o links

[schilld]

I have a query where I am trying to set the where criteria expression using an unlinked table. The unlinked table name is INV DATES. The criteria field is [THE_DATE]. I cannot really link the fields for the two tables since I am using an expression. This design works, but it goes really slow.

Any ideas?

SELECT DISTINCT DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER, DSSAPP_FULL_WIP_PROD_STATS.OPERATION, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY AS QUANTITY, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME AS [DATE], DSSAPP_FULL_WIP_PROD_STATS.LOT_HOLD_FLAG, DSSAPP_FULL_WIP_PROD_STATS.ACTIVE_FLAG, [INV DATES].THE_DATE, [INV DATES].WEEK_NUMBER_IN_YEAR, [INV DATES].MONTH_NUMBER_IN_YEAR INTO ROXES
FROM DSSAPP_FULL_WIP_PROD_STATS, [INV DATES]
WHERE (((DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R4*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "RR" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B0*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B4*") AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME)<=[INV DATES]![THE_DATE]) AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME)>[INV DATES]![THE_DATE] Or (DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME) Is Null) AND ((DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME)>=Now()-600) AND ((DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="STPPRD" Or (DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="EPIPRD"))
ORDER BY DSSAPP_FULL_WIP_PROD_STATS.OPERATION;

 

[Moniker]

This is probably going to involve a pretty big redesign, but before I go down that road, what is your definition of "really slow"?

Initial observations (and please format the SQL in the future):

SELECT DISTINCT 
    DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER
    ,DSSAPP_FULL_WIP_PROD_STATS.OPERATION
    ,DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME
    ,DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME
    ,DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY AS QUANTITY
    ,DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME AS [COLOR="Red"][DATE][/COLOR]
    ,DSSAPP_FULL_WIP_PROD_STATS.LOT_HOLD_FLAG
    ,DSSAPP_FULL_WIP_PROD_STATS.ACTIVE_FLAG
[COLOR="Blue"]    ,[INV DATES].THE_DATE
    ,[INV DATES].WEEK_NUMBER_IN_YEAR
    ,[INV DATES].MONTH_NUMBER_IN_YEAR [/COLOR]
INTO 
    ROXES
FROM
    DSSAPP_FULL_WIP_PROD_STATS, [INV DATES]
WHERE 
    (((DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R1*" OR 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R2*" OR 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R4*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R5*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "RR" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B0*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B1*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B2*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B5*" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B4*") AND 
    ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME) <=[INV DATES]![THE_DATE]) AND 
    ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME)>[INV DATES]![THE_DATE] Or 
    (DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME) Is Null) AND 
    ((DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME )>=Now()-600) AND
    ((DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="STPPRD" Or 
    (DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="EPIPRD"))
ORDER BY 
    DSSAPP_FULL_WIP_PROD_STATS.OPERATION
;

The red mark us a big no-no. It's a keyword and using that as a variable name is asking for issues. (Also note that "Week Number In Year" and "Month Number In Year" , both in blue, shouldn't be stored in a table as you can easily calculate both from any given date.)

The blue lines are the main issue. You have two tables here with no joins, which makes a cartesian product. WTF is that? Well, it's joining every record in one table to every record on the other table. For example, if I had two tables set up like this:

[u]t_Numbers[/u]   [u]t_Letters[/u]
1           A
2           B
3           C
4           D
5           E

And each field was named Test, then I made a query that asked for test from each table, without joining the tables, I'd get the following:

THE QUERY:
SELECT t_Numbers.Test, t_Letters.Test FROM t_Numbers, t_Letters

THE RESULT:
1A
1B
1C
1D
1E
2A
2B
2C
2D
2E
3A
3B
3C
3D
3E
4A
4B
4C
4D
4E
5A
5B
5C
5D
5E

This is because you're not joining on anything, so even though only five distinct records exist in each table, you're getting 25 records back. This is what's happening to you with your two tables because they have no joins, and yes, that will take some time to run, depending on the amount of data in each table.