Calling all Query Masters!! please 1

[aarellano]

Hello,

I am trying to build a query

I have two tables
the first table has sales from 04 to 06
the second has sales from 07
both have cust num, part num and inv amount

so here is what I am trying to do

I want to compare the customers that have bought something on 07 but have not bought anything on 04 trogh 06

and then either customers or parts sold in 07 but not sold in 04 trough 06


I am going a bit crazy with this one because here is what I have done so far.

unmatched Query with 04 to 06 sales to 07 sales linked by customer number

This gives me one figure on the 07 sales that makes no sence

 

[PWise]

dose this have to be in one query

 

[aarellano]

no it can be different queries the final I am going to send to excel at the end

 

[PWise]

Select [cust num] cusno
from [07] as a
left join [07] as b
on a.cusno=b.[cust num]
where b.[cust num] is null

 

[aarellano]

do i put this in the sql part of access?

 

[aarellano]

I am sorry but I don't think I am understanding very well. do I have do this in just one query or just ont the 04-06 query?

 

[lespaul]

change the LEFT JOIN table to the 04-06 table (is it a table or a query? Your OP says table, your last post says query....)


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[aarellano]

I am sorry I am so confused. It is a table

 

[lespaul]

I was trying to say that you needed to make the change bolded below:

Select [cust num] cusno
from [07] as a
left join [b][04-06][/b] as b
on a.cusno=b.[cust num]
where b.[cust num] is null

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[aarellano]

I had to add

'

a single quote

This is what the code looks like

Select '[cust_num]' as cusno
from [07] as a
left join [0406] as b
on a.cusno=b.[cust num]
where b.[cust num] is null

When I run it I get a small window that says Enter Parameter Value underneath that
b.cust num
underneath that I get an input box and then ok or close
if I hit ok I get one line with on top it says cusno
and then on each line I get [cust num]

 

[lespaul]

you shouldn't need the ' around [cust num]'

when you get prompted for a parameter that means that something in your query doesn't match what is in the table.

additionally you can't do this:

Select '[cust_num]' as [COLOR=red]cusno[/color]
from [07] as a
left join [0406] as b
on a.[COLOR=red]cusno[/color]=b.[cust num]
where b.[cust num] is null

you can't use the alias you created in the select statement as part of the join statement, you have to use the field name from the table.

make sure that you've got the field names correct from both tables.


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[aarellano]

I was following the suggested solution.
my customer number fields are
cust_num on both files
so when I tried your first suggestion

Select [cust num] cusno
from [07] as a
left join [04-06] as b
on a.cusno=b.[cust num]
where b.[cust num] is null

I added the _
that is when I got an error saying that I needed ' '

 

[lespaul]

SELECT A.[CUST NUM] FROM [07] As A
LEFT JOIN [04-06] As B ON A.[CUST NUM] = B.[CUST NUM]
WHERE B.[CUST NUM] IS NULL

 

[aarellano]

woooow

http://www that

is really cool!!!! Thank you so very much lespaul!!!! for all the help. I was up late last night was able to put together a few excel spread sheets and I got the same resulsts. Your way is much easier!!!

 

[lespaul]

JOINS are very powerful in SQL....check out this link for more on how to use them...

Thanks for the star!

leslie