By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Location, Location, Location

JBUDLARWOOD
P: 32
I am a novice and am trying to retrieve some data from our main computer system.
What I'm trying to do is locate any inventory that is not located in both locations.
I have an inv.loc table that has all the fields I need. When I put in the criteria "Not = 10 and 11821" it does not bring back the correct data.
Oct 6 '06 #1
Share this Question
Share on Google+
23 Replies


P: 3
Try (<> 10 And <> 11821) in the criteria of the query.
Oct 6 '06 #2

P: 30
As a supplement to that, go to the SQL view of your query, I think that the builder in access is neat in certain aspects, but for an experienced SQL guru, it's a very limiting tool. SQL itself isn't difficult, it can be as complex as you like, SELECT statements are easy to construct. The tool is handy for stuffing those pesky joins into your query though. Some people get confused as to which kind of join to use, an inner join, cross join, and the list goes on (and on). Seriously try to learn the SQL Script that is generated, and you'll be able to take access to the next level, you could even migrate to the more powerful SQL Server, (access hates it when you go over something like 65,000 records).
Oct 6 '06 #3

JBUDLARWOOD
P: 32
Thanks for the replies.
I tried the <>10 And <> 11821 and it didn't bring back any results when I know ther e are items in only one of two locations.
I also looked at the SQL statement and it appears to look correct.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id
HAVING (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 And (dbo_p21_view_inv_loc.location_id)<>11821))
ORDER BY dbo_p21_view_inv_loc.item_id;

Any other help would be appreciated.
Oct 9 '06 #4

PEB
Expert 100+
P: 1,418
PEB
Hi,

I suggest you to remove the condition with <>10 and the other one!

Execute your query and see on which rows the respective information appears!

If appears do Copy and than Paste in your query!

Best regards
Oct 14 '06 #5

JBUDLARWOOD
P: 32
PEB,
Here is the query.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
HAVING (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ))
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 17 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this instead:

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
WHERE (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 And (dbo_p21_view_inv_loc.location_id)<>11821))
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 17 '06 #7

JBUDLARWOOD
P: 32
I received an error that states:

You tried to execute a query that does not include the specified expression "location_id" as part of an aggregate function.
Oct 17 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
WHERE (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 And (dbo_p21_view_inv_loc.location_id)<>11821))
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id,
dbo_p21_view_inv_loc.location_id
ORDER BY dbo_p21_view_inv_loc.item_id;


I received an error that states:

You tried to execute a query that does not include the specified expression "location_id" as part of an aggregate function.
Oct 17 '06 #9

JBUDLARWOOD
P: 32
It came back with no data. I know there are items in 10 only and in 11821 only. I want to see those items that are not in both locations.
Oct 17 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Are all your ID tags supplier and location numbers or are any of them text.



It came back with no data. I know there are items in 10 only and in 11821 only. I want to see those items that are not in both locations.
Oct 17 '06 #11

JBUDLARWOOD
P: 32
item_id = Text
supplier_id = Number
location_id = Number
Oct 18 '06 #12

P: 7
Try this:

HAVING (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 ) OR
((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND
(dbo_p21_view_inv_loc.location_id)<>11821)))
Oct 18 '06 #13

JBUDLARWOOD
P: 32
Where in the query does this get inserted and what do I have to delete? I tried inserting it in the Group_By line but did not work. Again, I'm a novice to SQL & Access.
Oct 18 '06 #14

P: 7
Here's the entire statement using OR along with AND

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id
HAVING (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 OR(((dbo_p21_view_inv_loc.primary_supplier_id)=112 35 ) And (dbo_p21_view_inv_loc.location_id)<>11821))
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 18 '06 #15

JBUDLARWOOD
P: 32
I received a syntax error:
Missing operator in query expression
Oct 18 '06 #16

P: 7
Ooops....I didn't check the parentheses

Try this one.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id
HAVING (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10) OR((dbo_p21_view_inv_loc.primary_supplier_id)=1123 5) AND((dbo_p21_view_inv_loc.location_id)<>11821)))
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 18 '06 #17

JBUDLARWOOD
P: 32
Nope, same error.
Oct 18 '06 #18

P: 7
This is very similar to what you are trying to do using an Oracle table. It selects Parts based on Commodity Code = BHGEN-FG-OB whose Whs ID does not equal LAN-BH-FG as well as those whose Whs ID does not equal TAC-BH-FG.


SELECT SYSADM_PART.ID, SYSADM_PART.COMMODITY_CODE, SYSADM_PART.PRIMARY_WHS_ID
FROM SYSADM_PART
WHERE (((SYSADM_PART.COMMODITY_CODE)="BHGEN-FG-OB") AND ((SYSADM_PART.PRIMARY_WHS_ID)<>"LAN-BH-FG")) OR (((SYSADM_PART.COMMODITY_CODE)="BHGEN-FG-OB") AND ((SYSADM_PART.PRIMARY_WHS_ID)<>"TAC-BH-FG"))
ORDER BY SYSADM_PART.ID;


I set this up first in the Access Query grid then switched to the SQL view. Try setting up your query in the grid instead of SQL.

The OR statement is critical and sets the query so that records that meet criteria 1 and 2 OR criteria 1 and 3 are returned.


Also, remove the Group By until you know you have the records you want.

And last, if you must use SQL, change the word Having to Where.
Oct 18 '06 #19

JBUDLARWOOD
P: 32
Here is the SQL that I ended up with. It returns data but it is not correct. It is showing, for example, BQ-1 is in both 11821 & 10 on separate lines. This is an item I should not be seeing. The only thing I can think of is that it is seeing it as two separate records instead of one. This data is coming out of one database file so I don't think that is the case.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
WHERE (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10)) OR (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>11821))
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 18 '06 #20

Expert 100+
P: 1,221
Try this. Make a crosstab query. The Item number will be your row, the column will be the location, and the value can be the supplier, which you will limit to 11235 with a criteria.

The output of that query will be a dataset with two columns (assuming you have only two inventory locations). One column will be labeled "10" and one will be labeled "11821." Any row that has a Null for the supplier value in one column but not the other will be a hit for you.

So, you'll make another query that uses the first query for input, and you can specify a query item "Hit: (IIF(ISNULL([10]),0,1) + IIF(ISNULL([11821]),0,1))

So "Hit" will be a 0 if both are null, 1 if one of the two is null, and 2 if both are not null.

For this query item you'll make a criteria of =1 (only one of the two is not null). If you want also to get the rows that have neither location, then you'll use criteria of <=1 (one or both could be null).

I'm not 100% sure that those column names being numeric will work. You can specify different column labels, but let's not go there unless this does not work.

Good luck.
Jim
Oct 19 '06 #21

P: 7
Can you do a print screen of the QBE and post it here? That might help to see how it's structured.


Here is the SQL that I ended up with. It returns data but it is not correct. It is showing, for example, BQ-1 is in both 11821 & 10 on separate lines. This is an item I should not be seeing. The only thing I can think of is that it is seeing it as two separate records instead of one. This data is coming out of one database file so I don't think that is the case.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
WHERE (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10)) OR (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>11821))
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 19 '06 #22

P: 8
I am a novice and am trying to retrieve some data from our main computer system.
What I'm trying to do is locate any inventory that is not located in both locations.
I have an inv.loc table that has all the fields I need. When I put in the criteria "Not = 10 and 11821" it does not bring back the correct data.

Try a sub query in your where clause using IN

...dbo_p21_view_inv_loc.location_id NOT IN (10,11821)
Oct 19 '06 #23

JBUDLARWOOD
P: 32
Peter,
I tried it as listed below and it is still not correct. It is still showing items in both locations.

SELECT dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
FROM dbo_p21_view_inv_loc
WHERE (((dbo_p21_view_inv_loc.primary_supplier_id)=11235 ) AND ((dbo_p21_view_inv_loc.location_id)<>10 And (dbo_p21_view_inv_loc.location_id)<>11821) IN (dbo_p21_view_inv_loc.location_id) NOT IN (10,11821))
GROUP BY dbo_p21_view_inv_loc.item_id, dbo_p21_view_inv_loc.primary_supplier_id, dbo_p21_view_inv_loc.location_id
ORDER BY dbo_p21_view_inv_loc.item_id;
Oct 20 '06 #24

Post your reply

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