Join two queries?

[dtinsley]

Ok, I have having a problem joing these two queries. Can anyone please help me out here. These are the results of my two queries:

These are the values in the A1 field of QueryA

• 
  COCKBURN 2
  GT KW 1
  GTN 1
  IPP GT1
  IPP GT2
  KALG F5
  KALG F6
  KWIN C6
  MUJA 1
  MUJA 2
  MUJA 3
  MUJA 4
  MUJA C5
  MUJA C6
  MUJA D7
  MUJA D8

These are the values in fields A1 and X1 in QueryB:

COCKBURN      86.83
GT KW1          10.44
GTN 1            16.13
IPP GT1          62.84
IPP GT2          62.56
KALG F5          15.12
KALG F6          20.83
KWIN C6          37.78
MUJA 1            23.44
MUJA 3            8.69
MUJA 4            23.44
MUJA C5          84.56
MUJA C6          89.01
MUJA D7          86.22
MUJA D8          84.65

In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:

COCKBURN      86.83
GT KW1          10.44
GTN 1            16.13
IPP GT1          62.84
IPP GT2          62.56
KALG F5          15.12
KALG F6          20.83
KWIN C6          37.78
MUJA 1            23.44
MUJA 3            8.69
MUJA 2
MUJA 4            23.44
MUJA C5          84.56
MUJA C6          89.01
MUJA D7          86.22
MUJA D8          84.65

How can this be done. I need this in another query. I have tried the join statements, but I get errors every time. Thanks in advance.

 

[dtinsley]

This is one statement I have tried:

SELECT *
FROM QueryA
LEFT OUTER JOIN
QueryB
ON QueryA.A1 = QueryB.A1

but I get an error (field is to small to accept the value....)

 

[KeithG]

You need to use a left join with QueryA(left side) and QueryB(right side) on field A1.

 

[dtinsley]

SELECT *
FROM QueryA
LEFT JOIN
QueryB
ON QueryA.A1 = QueryB.A1

like this?

 

[KeithG]

that should work.

 

[dtinsley]

that produces a syntax error in JOIN operation...

 

[KeithG]

Are the fields the same data type?

 

[dtinsley]

yeah, they are the same.

 

[dtinsley]

The only field in QueryA is the A1 field, which hold every customer. QueryB has 2 fields, A1, which is the customers who made an order on day 1, and X1, which is the value they spent. If you notice, QueryA has 16 recordsets, where QueryB only has 15 because one customer didnt have a purchase on day 1, so they arent included in QueryB. I can't figure this out...

 

[KeithG]

Can you post your db, a left join is definetly what you need I just don't know why you are receiving an error.

 

[dtinsley]

its huge. let me make a db with just what you would need. give me a few mins

 

[KeithG]

You have to run the file through WinZip to post it

 

[dtinsley]

Ok, hope this will work. I just made it real quick. Focus on QueryA and QueryB.

Like I said I need a new query that has the 16 recordsets from QueryA A1 field with the 15 recordsets in the correct place from the QueryB X1 field. Thanks alot for the help so far...