T
tkosel
Perhaps someone can enlighten me. I am relatively new at creating SQL from
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.
SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;
The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.
SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;
The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?
Thanks for helping a rookie out!
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.
SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;
The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.
SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;
The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?
Thanks for helping a rookie out!