Location, Location, Location

[JBUDLARWOOD]

I received a syntax error:
Missing operator in query expression

[Thall]

Ooops....I didn't check the parentheses

Try this one.

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_vie w_inv_loc.prima ry_supplier_id) =11235) AND((dbo_p21_vi ew_inv_loc.loca tion_id)<>11821 )))
ORDER BY dbo_p21_view_in v_loc.item_id;

[JBUDLARWOOD]

Nope, same error.

[Thall]

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.COM MODITY_CODE, SYSADM_PART.PRI MARY_WHS_ID
FROM SYSADM_PART
WHERE (((SYSADM_PART. COMMODITY_CODE) ="BHGEN-FG-OB") AND ((SYSADM_PART.P RIMARY_WHS_ID)< >"LAN-BH-FG")) OR (((SYSADM_PART. COMMODITY_CODE) ="BHGEN-FG-OB") AND ((SYSADM_PART.P RIMARY_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.

[JBUDLARWOOD]

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_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)) OR (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>11821))
ORDER BY dbo_p21_view_in v_loc.item_id;

[jimatqsi]

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

[Thall]

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_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)) OR (((dbo_p21_view _inv_loc.primar y_supplier_id)= 11235) AND ((dbo_p21_view_ inv_loc.locatio n_id)<>11821))
ORDER BY dbo_p21_view_in v_loc.item_id;

[PeterDavis]

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.locati on_id NOT IN (10,11821)

[JBUDLARWOOD]

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_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) IN (dbo_p21_view_i nv_loc.location _id) NOT IN (10,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;