Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012

    Unanswered: Query to look up 3 tables and generate a report!

    Hi Gurus,

    I'am quite new to SQL but keep using it

    I need some help with generating a report from few tables as in the following example. It would be helpful if anyone can explain me how to do this job:

    Table 1:
    Quote Color Grade
    1111 001 111
    1112 002 222
    1113 003 333
    1114 004 444
    1115 005 555

    Table 2:
    Id Color
    001 Green
    002 Violet
    003 Red
    004 Black
    005 Orange

    Table 3:
    ID Grade
    111 Grade A
    222 Grade B
    333 Grade C
    444 Grade D
    555 Grade E

    Quote Color Grade
    1111 Green Grade A
    1112 Violet Grade B
    1113 Red Grade C
    1114 Black Grade D
    1115 Orange Grade E

    I'll be using this on a mass scale for about 20 to 30 tables in a VBA module. Please help me understand this.

    Thanks in advance,


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    use a JOIN

    Select Table1.Quote, Table2.Colour, Table3.Grade from Table1
    JOIN Table2 on Table2.ID = Table1.Colour
    JOIN Table3 on Table3.ID = Table1.Grade

    bear in mind Access uses its own form of Join so you will need toconvert the above to correct Access/JET SQL.

    a simpler way of doing this process is however to make certain there are Referential Integrity Links between the tables as part of the design
    then pull up the various tables into the query designer and the JOINS should be established for you.

    failing that you can plonk the required tables onto the query designer and establish the links between the tables manually as part of the query definition.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Thank you very much Healdem. I appreciate your help.

    I'll try to implement and check the result.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts