Isn't there a better way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Experts:

Below is a working querry that looks for a search on one table that then
matches to a table Q_AEXP99 by the ref field, which then matches to a
receipt_details by using the receipt field. Instead of it looking for the
range in one table I want to add another table for it to search into. And
also match those records found on that table with the Q-AEXP99 and
receipt_details. Can someone please check the code and let me know if there
is a good way to add the extra table?

SELECT keycode.Keystart, keycode.Keystop, keycode.Kstart2, keycode.Kstop2,
Q_AEXP99.Film_Title, Q_AEXP99.requested_by, receipt_details.Tape_CR,
keycode.Scene, keycode.Pullscene, keycode.CR, keycode.PullCR,
receipt_details.Description, receipt_details.ref, keycode.Barcode,
keycode.Kdate, keycode.Tk1, keycode.Tk2
FROM keycode INNER JOIN (receipt_details INNER JOIN Q_AEXP99 ON
receipt_details.[Receipt#] = Q_AEXP99.Receipt) ON keycode.REF =
receipt_details.ref
WHERE (((keycode.Keystart) Like [Start Range - preceed with *]) AND
((keycode.Keystop) Like [stop range - follow by *]));

This is what this is supposed to be doing. It is supposed to be searching
for a numerical range in table keycode and table keycodepull(I ha, it is
supposed to pull about 8 fields of information from each table. Within these
fields there is a field called ref in both the keycode and they keycodepull.
Now this field is supposed to be matched up with the q_aexp99 table, which in
turn has a field called receipt wich is supposed to be matched up to the
receipt in receipt_details table.

I cannot get the right code to run, please help! If you have any questions
please feel free to post I will be checking this frequently.
Thanks

Celia
 
Have you tried a UNION query? Something like this (this is untested, but it
may get you started):

SELECT keycode.Keystart, keycode.Keystop, keycode.Kstart2, keycode.Kstop2,
Q_AEXP99.Film_Title, Q_AEXP99.requested_by, receipt_details.Tape_CR,
keycode.Scene, keycode.Pullscene, keycode.CR, keycode.PullCR,
receipt_details.Description, receipt_details.ref, keycode.Barcode,
keycode.Kdate, keycode.Tk1, keycode.Tk2
FROM keycode INNER JOIN (receipt_details INNER JOIN Q_AEXP99 ON
receipt_details.[Receipt#] = Q_AEXP99.Receipt) ON keycode.REF =
receipt_details.ref
UNION
SELECT keycodepull.Keystart, keycodepull.Keystop, keycodepull.Kstart2,
keycodepull.Kstop2,
Q_AEXP99.Film_Title, Q_AEXP99.requested_by, receipt_details.Tape_CR,
keycodepull.Scene, keycodepull.Pullscene, keycodepull.CR,
keycodepull.PullCR,
receipt_details.Description, receipt_details.ref, keycodepull.Barcode,
keycodepull.Kdate, keycodepull.Tk1, keycodepull.Tk2
FROM keycodepull INNER JOIN (receipt_details INNER JOIN Q_AEXP99 ON
receipt_details.[Receipt#] = Q_AEXP99.Receipt) ON keycodepull.REF =
receipt_details.ref
WHERE (((Keystart) Like [Start Range - preceed with *]) AND
((Keystop) Like [stop range - follow by *]));

This assumes that the field names in 'keycodepull' are the same as in
'keycode'. If not, you will need to use aliasing to rename the fields in the
queries (SELECT <field> AS <newname>, ...). Note also that the table name is
removed from the fields in the WHERE clause. Finally, you may have a problem
with double prompts for the range values. In that case, you could use a form
to input the ranges and refer to the form controls from the queries to get
the values. You can search the newsgroup for examples of this.

Carl Rapson

kitty said:
Experts:

Below is a working querry that looks for a search on one table that then
matches to a table Q_AEXP99 by the ref field, which then matches to a
receipt_details by using the receipt field. Instead of it looking for the
range in one table I want to add another table for it to search into. And
also match those records found on that table with the Q-AEXP99 and
receipt_details. Can someone please check the code and let me know if
there
is a good way to add the extra table?

SELECT keycode.Keystart, keycode.Keystop, keycode.Kstart2, keycode.Kstop2,
Q_AEXP99.Film_Title, Q_AEXP99.requested_by, receipt_details.Tape_CR,
keycode.Scene, keycode.Pullscene, keycode.CR, keycode.PullCR,
receipt_details.Description, receipt_details.ref, keycode.Barcode,
keycode.Kdate, keycode.Tk1, keycode.Tk2
FROM keycode INNER JOIN (receipt_details INNER JOIN Q_AEXP99 ON
receipt_details.[Receipt#] = Q_AEXP99.Receipt) ON keycode.REF =
receipt_details.ref
WHERE (((keycode.Keystart) Like [Start Range - preceed with *]) AND
((keycode.Keystop) Like [stop range - follow by *]));

This is what this is supposed to be doing. It is supposed to be searching
for a numerical range in table keycode and table keycodepull(I ha, it is
supposed to pull about 8 fields of information from each table. Within
these
fields there is a field called ref in both the keycode and they
keycodepull.
Now this field is supposed to be matched up with the q_aexp99 table, which
in
turn has a field called receipt wich is supposed to be matched up to the
receipt in receipt_details table.

I cannot get the right code to run, please help! If you have any
questions
please feel free to post I will be checking this frequently.
Thanks

Celia
Click to expand...
 
Back
Top