Location, Location, Location

[JBUDLARWOOD]

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.

[JJ48]

Try (<> 10 And <> 11821) in the criteria of the query.

[anaphaxeon]

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).

[JBUDLARWOOD]

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_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
FROM dbo_p21_view_in v_loc
GROUP BY dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id
HAVING (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>10 And (dbo_p21_view_i nv_loc.location _id)<>11821))
ORDER BY dbo_p21_view_in v_loc.item_id;

Any other help would be appreciated.

[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

[JBUDLARWOOD]

PEB,
Here is the query.

SELECT dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
FROM dbo_p21_view_in v_loc
GROUP BY dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
HAVING (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235))
ORDER BY dbo_p21_view_in v_loc.item_id;

[MMcCarthy]

Try this instead:

SELECT dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
FROM dbo_p21_view_in v_loc
WHERE (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235 ) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>10 And (dbo_p21_view_i nv_loc.location _id)<>11821))
GROUP BY dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id
ORDER BY dbo_p21_view_in v_loc.item_id;

[JBUDLARWOOD]

I received an error that states:

You tried to execute a query that does not include the specified expression "location_i d" as part of an aggregate function.

[MMcCarthy]

SELECT dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
FROM dbo_p21_view_in v_loc
WHERE (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235 ) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>10 And (dbo_p21_view_i nv_loc.location _id)<>11821))
GROUP BY dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id,
dbo_p21_view_in v_loc.location_ id
ORDER BY dbo_p21_view_in v_loc.item_id;

I received an error that states:

You tried to execute a query that does not include the specified expression "location_i d" as part of an aggregate function.

[JBUDLARWOOD]

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.

[MMcCarthy]

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.

[JBUDLARWOOD]

item_id = Text
supplier_id = Number
location_id = Number

[Thall]

Try this:

HAVING (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235 ) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>10 ) OR
((dbo_p21_view_ inv_loc.primary _supplier_id)=1 1235 ) AND
(dbo_p21_view_i nv_loc.location _id)<>11821)))

[JBUDLARWOOD]

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.

[Thall]

Here's the entire statement using OR along with AND

SELECT dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id, dbo_p21_view_in v_loc.location_ id
FROM dbo_p21_view_in v_loc
GROUP BY dbo_p21_view_in v_loc.item_id, dbo_p21_view_in v_loc.primary_s upplier_id
HAVING (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235 ) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>10 OR(((dbo_p21_vi ew_inv_loc.prim ary_supplier_id )=11235 ) And (dbo_p21_view_i nv_loc.location _id)<>11821))
ORDER BY dbo_p21_view_in v_loc.item_id;