Query to look for differences 1

[dmkennard2]

Hi,
I have a stock database in access. In the database is flat table with locations, codes Qty's etc....

Each location is split into Back and Front with a maximum capacity to hold 2 codes.
I cant seem to figure out how to create a query that will display locations that have different codes in the Front from the Back or Vice Versa.

Any help much appreciated.

Dazz

 

[Remou]

Have you looked at a Find Unmatched Query (query wizard). Is it what you want?

 

[dmkennard2]

No i dont think so.

If i have:
1 - Loc1Back = Code1 & Loc1Front = Code1
2 - Loc2Back = Code1 & Loc2Front = Code2
3 - Loc3Back = Code2 & Loc3Front = Code1

Then the query will return records 2 and 3 because these are the records with 2 codes in.

I have tried to count codes for a location and only display the ones that show 2 or more, but this is counting the amount of pallets in each location and this can 2 in each!

Thanks
Dazz

 

[lespaul]

Can you provide some sample data from the tables and your expected results?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Remou]

How about:
[tt]SELECT tblQ.Field1, Mid([Field1],InStr([field1],"Back")+7,5) AS Chk1, Mid([Field1],InStr([field1],"Front")+8) AS Chk2
FROM tblQ
WHERE (((Mid([Field1],InStr([field1],"Back")+7,5))<>Mid([Field1],InStr([field1],"Front")+8)));[/tt]

Have you considered a redesign to split this field into two?

 

[dmkennard2]

Hi Remou, i tried your code and it takes the B ad F and puts it into a seperate field

Sample Data as requested
Code Location
123 H01L-001-3B
123 H01L-001-3B
1234 H01L-001-3F
1234 H01L-001-3F
----------------------------------
10000 H01L-001-4B
10000 H01L-001-4B
10000 H01L-001-4F
10000 H01L-001-4F

With the above data the query will return location H01L-001-3B as this is the only location wiht more than one Code.
If it helps i also have the Location split down like this:
Aisle - H01
Side - L
Location - 001
Level - 4
Back or Front - B

Thanks
Dazz

 

[nicsin]

try this (replace table1 with your table name)

SELECT 
DISTINCT Loc
FROM (
SELECT 
Left(location,Len(location)-1) AS Loc, code, Count(code)
FROM 
table1
GROUP BY 
Left([location],Len(location)-1), code
HAVING Count(code)=1
)

N

 

[dmkennard2]

Excellent that works a treat.
Thank you very much.

One more question if i can be so bold?
If i wanted to see the codes associated with these locations in a report, how can i add these?
I have a list of codes in a seperate table as well as the table i am using in the above query?

Thanks again.
Dazz

 

[nicsin]

unless you give us details such as tables' structure (fields,datatypes), sample data and expected results can only guess. so my guess is

use my query in the where clause:

SELECT 
code 
FROM 
table 
WHERE 
location in (
myQuery
)

 

[dmkennard2]

Hi,
Going back to my original question, this is the code that i end up with.

SELECT DISTINCT [%$##@_Alias].Location
FROM [SELECT Left(WCS_Location,Len(WCS_Location)-1) AS Location, Material, Count(Material)
FROM 
tblMainData
GROUP BY 
Left([WCS_Location],Len(WCS_Location)-1), Material
HAVING Count(Material)=1]. AS [%$##@_Alias];

Not sure where the "[%$##@_Alias]."comes from!!

Every day i delete all data in the Material table and the MainData table. After i have done this i get an error when running the query again. The error is "Invalid Procedure Call"

Any ideas?

Dazz

 

[nicsin]

this is a better solution

select location from
(SELECT 
Left(WCS_Location,Len(WCS_Location)-1) as location
FROM 
tblMainData
GROUP BY 
Left(WCS_Location,Len(WCS_Location)-1), material)
group by location
having count(location)>1

Every day i delete all data in the Material table and the MainData table.

I am not sure what you mean. First of all we are only getting data from one table in this query, tblMainData, and second this query will get nothing if the table is empty. Also, it can't handle null values for WCS_Location.

N

 

[nicsin]

the alias "[%$##@_Alias]." is added by access. do not worry about it.

N

 

[dmkennard2]

Sorry i should have explained a bit better.
Each day i delete all the data in the MainData table and re-import fresh SAP data back into the table. We use Access to manipulate the data easily and produce some reports.

After i have re-imported the data i get the error "Invalid Procedure Call"

I have just tried your new code and am getting the error even though its in a new query.

Dazz

 

[dmkennard2]

Update - I took out the records where the WCS_Location are Null and it works. Is there a way of getting it to run even if WCS_Location is Null?

Dazz

 

[nicsin]

thought so. try this

SELECT loc
FROM (SELECT 
iif(location is not null, Left(location,Len(location)-1),'') as loc
FROM 
table1
GROUP BY 
iif(location is not null,Left(location,Len(location)-1),''), code
)
GROUP BY loc
HAVING count(loc)>1;

N

 

[dmkennard2]

Nope,Still getting the error, this is my code:

SELECT Location
FROM [SELECT 
iif(WCS_Location is not null, Left(WCS_Location,Len(WCS_Location)-1),'') as Location
FROM 
tblMainData
GROUP BY 
iif(WCS_location is not null,Left(WCS_Location,Len(WCS_Location)-1),''), Material
]. AS [%$##@_Alias]
GROUP BY Location
HAVING count(Location)>1;

I can work around this by using an update query to enter "No Location" into the Nulls when i import the data.

Dazz

 

[nicsin]

that's one solution i suppose... how do you run the query? inside access, from a vb app, etc?

N

 

[dmkennard2]

I have a spreadsheet with VB i have written that formulates the extract from SAP. This then saves to a specific area.
In Access i have a Control Panel (Switchboard) that has a button that runs a Macro to import that data from the spreadsheet and then run the update query to insert the "No Location" into Null locations.

Probably not the best way to to things, but it works.

Dazz