473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Join two queries?

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:
Expand|Select|Wrap|Line Numbers
  1. COCKBURN      86.83
  2. GT KW1          10.44
  3. GTN 1            16.13
  4. IPP GT1          62.84
  5. IPP GT2          62.56
  6. KALG F5          15.12
  7. KALG F6          20.83
  8. KWIN C6          37.78
  9. MUJA 1            23.44
  10. MUJA 3            8.69
  11. MUJA 4            23.44
  12. MUJA C5          84.56
  13. MUJA C6          89.01
  14. MUJA D7          86.22
  15. MUJA D8          84.65
  16.  
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:

Expand|Select|Wrap|Line Numbers
  1. COCKBURN      86.83
  2. GT KW1          10.44
  3. GTN 1            16.13
  4. IPP GT1          62.84
  5. IPP GT2          62.56
  6. KALG F5          15.12
  7. KALG F6          20.83
  8. KWIN C6          37.78
  9. MUJA 1            23.44
  10. MUJA 3            8.69
  11. MUJA 2
  12. MUJA 4            23.44
  13. MUJA C5          84.56
  14. MUJA C6          89.01
  15. MUJA D7          86.22
  16. MUJA D8          84.65
  17.  
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.
May 3 '06 #1
11 8153
CaptainD
135 100+
It would be easier and more helpful if you posted information about your tables and show us your select statments instead of just the information you're getting.
May 3 '06 #2
I am getting the values from two different locations. The A1 field in QueryA is the locations of customers from the customer file. The A1 field is the locations for customers that had a purchase on day 1. The X1 field is the purchase amount for that day. It is coming from another database.
May 3 '06 #3
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....)
May 3 '06 #4
wlc04
70
What program are you using?
May 3 '06 #5
Access 2003
May 3 '06 #6
wlc04
70
You should just be able to create a new query with those queries in it - join fields A1 and add the desired fields to the query (I personally use the grid rather than SQL, so I can see all fields). Another option is to add the tables themselves to the query join on the fields and then add the desired options from each table.
May 3 '06 #7
i am wanting it to do this on the fly because these values will change from day to day
May 3 '06 #8
CaptainD
135 100+
Again, if you post some table structure and your select statments for the queries it would be easier for others to help. If you have two queries then you have access to the tables even if they are in other databases.

If we know the two table structures we might be able to create a query straight off the tables. Or, if we can see how the select statments are put together we can figure out why you are not getting the information you want.

i am wanting it to do this on the fly because these values will change from day to day
What do you mean the "values will change"? If you're talking about the data then running the query gets the updated data when you run it. Or do you mean you'll have to change the queries "on the fly"?
May 3 '06 #9
wlc04
70
You say the data will change daily - are you connecting directly to the tables where the data resides or are you exporting data from the other dbs and importing into yours? If you connect directly then once you get your queries created the way you like then you're set - the results are dynamic. If you're doing exports (I personally have to do this a lot!!) then you may want to set something up to run automatically everyday which will update the tables in your db.

If your A1 fields are not wanting to join you might make sure they are the same data type - this will cause you errors.
May 3 '06 #10
Thanks for the help guys. It seems one of my queries was screwed up. No clue why, but i started it all from scratch again (both queried I previously had and the new query I was working on) on the LEFT JOIN worked as it should of the entire time. Thanks for the help..
May 4 '06 #11
CaptainD
135 100+
That's why we post code and sql statements, not just results, so others can look for errors, mis-matches etc.
May 4 '06 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
1
by: Beachvolleyballer | last post by:
hi there anyone had an idea to join following 2 queries to 1???? ----- QUERY 1 --------------------------------------------- SELECT TMS_CaseF_2.Name AS TCDomain_0, TMS_CaseF_3.Name AS...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
2
by: HS Hartkamp | last post by:
Hi all, I have a table with analysis-results for various months. An item can -for a particular month- have the result 'list1', 'list2' or 'list3' depending on the result of the analysis. ...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
3
by: mariohiga | last post by:
Hello I've a really big doubt :) I've this two alternatives SELECT * FROM T1 a INNER JOIN T1 b ON a.F1 = b.F1 AND a.F2 = 1 SELECT *
14
by: cjakeman | last post by:
Hi, Solved a little mystery yesterday when I built a form that combined 2 tables with a 1:M relationship and relational integrity. All the correct data was visible on the form but, if I tried to...
2
by: frederikengelen | last post by:
Hello all, We are seeing strange behaviour for queries on a table we need to convert data from. We try to find out whether table A(B_CONV_ID) contains data that does not exists in table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.