|
| |
|
|
milo
Posts: 50
Joined: 5/7/2004
From: Calgary, Canada
Status: offline
 |
A "not equal" query... - 5/5/2006 17:58:00
Here's a new challenge/problem I'm having.
I have two tables which need to link (somehow) in two ways...
1) (Dates) TableA.acct_month needs to be between TableB.flow_month and TableB.inv_month
2) (Locations) TableA.bp_name cannot equal TableB.bp_name
Essentially, I'm looking for the receipts that don't match up for a given month.
I've tried this... (but doesn't work at all)
SELECT [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [ProServ NOVA].acct_month
FROM [ProServ NOVA] INNER JOIN [Receipt Commodity Charges] ON ([ProServ NOVA].receipt_location <> [Receipt Commodity Charges].station_name) AND ( [Receipt Commodity Charges].inv_month > [ProServ NOVA].acct_month > [Receipt Commodity Charges].flow_month)
GROUP BY [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [ProServ NOVA].acct_month;
I was thinking about a "Union" or "Where" join, but don't know exactly how to create these properly...
(An Example of my failed "Where" join)
SELECT [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [Receipt Commodity Charges].flow_month, [Receipt Commodity Charges].inv_month
FROM [ProServ NOVA], [Receipt Commodity Charges]
WHERE (([ProServ NOVA].receipt_location<>[Receipt Commodity Charges].[station_name]) AND ([ProServ NOVA].acct_month BETWEEN [Receipt Commodity Charges].[flow_month] AND [Receipt Commodity Charges].inv_month))
GROUP BY [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [Receipt Commodity Charges].flow_month, [Receipt Commodity Charges].inv_month;
Any thoughts?
< Message edited by milo -- 5/5/2006 18:26:51 >
|
|
|
|
rubyaim
Posts: 757
Joined: 6/22/2005
Status: offline
|
RE: A "not equal" query... - 5/5/2006 19:54:49
What's the GROUP BY doing? It sounds like you just need a to Select, Join and specify Where.
Are you getting any specific errors or just no data?
_____________________________
Sally
| |
|
|
|
yb2
Posts: 653
Joined: 1/30/2006
Status: offline
|
RE: A "not equal" query... - 5/6/2006 8:07:02
First thing - use aliases, they make everything much easier to read
SELECT R.station_num, R.station_name, P.acct_month
FROM [ProServ NOVA] as P
INNER JOIN [Receipt Commodity Charges] as R
ON (P.receipt_location <> R.station_name) AND ( R.inv_month > P.acct_month > R.flow_month)
GROUP BY R.station_num, R.station_name, P.acct_month;
Phew, so much better!
quote:
given month.
This little nugget of info means you need a WHERE clause.
quote:
don't match up
This nugget means you are looking for an OUTER join
Try
SELECT R.station_num, R.station_name, P.acct_month
FROM [ProServ NOVA] as P
LEFT OUTER JOIN [Receipt Commodity Charges] as R
ON P.receipt_location = R.station_name
WHERE P.acct_month = varGivenMonth AND R.inv_month > varGivenMonth AND R.flow_month < varGivenMonth
AND R.station_name IS NULL
GROUP BY R.station_num, R.station_name, P.acct_month;
I assume you're using Access from other posts you've made. varGivenMonth is an input variable like you'd use in a query. This SQL statement will bring back all the P.receipt_location rows, and so they will have some that don't match with R.station_name, so you search for those in the WHERE clause. The other part of the WHERE is taken from your earlier join attempts.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
milo
Posts: 50
Joined: 5/7/2004
From: Calgary, Canada
Status: offline
|
RE: A "not equal" query... - 5/11/2006 18:33:26
OK, I got this code to not throw up errors anymore, but it doesn't return anything at all...
I am absolutely positive that there should be approximately 25 records returned for each month.
This is exactly what I'm trying to do (just in case there is any confusion or questions):WHERE (R.inv_month > P.acct_month > R.flow_month) AND (R.station_num <> P.receipt_id)
and I believe we are on the right track, but we're missing something...
latest code, in all it's glory:SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month
FROM [Receipt Commodity Charges] AS R LEFT JOIN [ProServ NOVA] AS P
ON R.station_num = P.receipt_id
WHERE ([R].[inv_month]>[P].[acct_month]>[R].[flow_month])
AND (R.station_num<>[P].[receipt_id])
GROUP BY P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month;
< Message edited by milo -- 5/11/2006 18:40:47 >
| |
|
|
|
yb2
Posts: 653
Joined: 1/30/2006
Status: offline
|
RE: A "not equal" query... - 5/12/2006 4:37:20
I'd try removing all of the WHERE clause and then adding it bit by bit to check that the main statement and the clauses you add are bringing back the data you expect.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
yb2
Posts: 653
Joined: 1/30/2006
Status: offline
|
RE: A "not equal" query... - 5/16/2006 13:20:09
quote:
SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month
FROM [Receipt Commodity Charges] AS R LEFT JOIN [ProServ NOVA] AS P
ON R.station_num = P.receipt_id
WHERE ([R].[inv_month]>[P].[acct_month]>[R].[flow_month])
AND (R.station_num<>[P].[receipt_id])
GROUP BY P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month;
I found out today that you can use a range for a join - you learn something new every day!
It means you could try this
SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month
FROM [ProServ NOVA] AS P
INNER JOIN [Receipt Commodity Charges]
ON P.acct_month BETWEEN R.inv_month AND R.flow_month
WHERE R.station_num <> P.receipt_id
If that brings back any data you could play around with the join and the where to see if you can get the right stuff back.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
milo
Posts: 50
Joined: 5/7/2004
From: Calgary, Canada
Status: offline
|
RE: A "not equal" query... - 5/17/2006 13:02:31
OK, so it seems that I got all excited because I thought we were close to finished...
It seems that there's a flaw in the logic.
So, there are two tables with a location id in each (P.receipt_id & R.station_num). The [P] table is always missing approximately 25 locations (every month). These locations should match up exactly, alas, they don't...
So, this query is trying to find the locations that are not in [P] that are in [R]... (for a given month, i.e. [R].[inv_month]>[P].[acct_month]>[R].[flow_month])
When I run this query... SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month
FROM [ProServ NOVA] AS P
INNER JOIN [Receipt Commodity Charges] AS R
ON P.receipt_id <> R.station_num
WHERE (P.acct_month BETWEEN R.inv_month AND R.flow_month)
AND (P.acct_month <> R.inv_month) AND (P.acct_month <> R.flow_month);
Because there are about 300 locations each month, of course these 300 don't match 299 locations in the other table. Consequently, I get 300! (factorial) results with this query.
THE NUTSHELL:
How do I create a query that shows which locations present in the [R] table that are not present in the [P] table?
Thank you guys. You've been a real help.
| |
|
|
|
milo
Posts: 50
Joined: 5/7/2004
From: Calgary, Canada
Status: offline
|
RE: A "not equal" query... - 5/17/2006 17:51:34
So, I'm slightly a genius... I figured this baby out.
Check this out:SELECT DISTINCT R.station_num, R.station_name, R.inv_month, R.flow_month
FROM [Receipt Commodity (Normalize)] AS R
WHERE (((Exists (SELECT P.receipt_id, P.acct_month FROM [ProServ (Normalize)] AS P
WHERE (R.station_num = P.receipt_id) AND
(P.acct_month BETWEEN R.inv_month AND R.flow_month) AND
(P.acct_month <> R.inv_month) AND
(P.acct_month <> R.flow_month)))=False));
Originally I had simpler code...SELECT * FROM R
WHERE NOT EXISTS
(SELECT * FROM P
WHERE A.X = B.X AND A.Y > B.Y AND ... etc)
But Access automatically rewrote the code when I ran the query and looked at it in design view.
But that's the answer (above).
| |
|
|
|
milo
Posts: 50
Joined: 5/7/2004
From: Calgary, Canada
Status: offline
|
RE: A "not equal" query... - 5/17/2006 17:52:54
P.S. you guys were excellent!
Thank you for your help. I really appreciate all you've done.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
