finding a condition in a query (1 Viewer)

[token_remedie]

Hey all,
I am trying to find out if a record exists in a query, and if it does then run another one, I was thinking something like: while not .eof do until if strSQL = true (sql code) compare table 1 and 2 - update table 3 else next record.

Basically I've got a query that finds duplicates between two tables on two fields, and if a record from redisttable is in this query (and hence a duplicate) then i want to check location, if that's the same in both tables, ignore, however if it's different append to transactions (table 3).

Here's what I have to far:

Query:

SELECT Master.UID, Master.[Asset Number], Master.[Serial No#], Master.LOCATION, Master.F10
FROM Master LEFT JOIN redisttable ON Master.[Asset Number] = redisttable.[Asset Number] AND Master.[serial no#] = redisttable.[serial no#]
WHERE (((redisttable.[Asset Number]) Is Not Null) AND ((redisttable.[serial no#]) Is Not Null));

then I tried this:
Set rs = CurrentDb.OpenRecordset("Master Without Matching redisttable")

but I dont really know what to do with it.
Hope that make sense.

 

[vbaInet]

You can just use a DCount() to see whether it records a number greater than (or not equal to) 0, and perform any necessary operations if it is.

 

[token_remedie]

do I just put that in a loop?
something like while eof = false do until eof if dcount(query) >1 do stuff else next?
by the by I'm fairly new at this so assistance and knowledge is greatly appreciated

 

[vbaInet]

Some aircode:

If DCount() is duplicate in table 1 and table 2
     If DCount("of Location") is not the same in table 1 and table 2
          DoCmd.RunSQL "UPDATE ..."
     End If
End If

 

[token_remedie]

you are a legend! so I've got the dcount to work:

If DCount("*", "[master without matching redisttable]", "LOCATION = [NEW LOCATION]") > 0 Then

I made a query that joins by asset number and serial number so am I right in assuming that the next part will go something like this?

loop until eof and for every record, IF DCount(above dcount statement) >0 then update the table (by using an update sql query in here)
else
keep looping until end of query?

 

[vbaInet]

Basically I've got a query that finds duplicates between two tables on two fields, and if a record from redisttable is in this query (and hence a duplicate) then i want to check location, if that's the same in both tables, ignore, however if it's different append to transactions (table 3).

Perhaps you can do it all in a query.

1. In your Duplicate query, set a new column with the value

table1.Location <> table2.Location

2. Under the criteria put True
3. This should qualify all the criteria you mentioned above. Now use this query to create an UPDATE query.

By the way, why are you duplicating data? Is this a one off exercise or a continuous process?

 

[token_remedie]

well its an asset management system where the assets move around, and im trying to put their previous location into another table and then update the main table with the current location. basically like a library system keeping track of who borrowed what books, in fact it's exactly the same just with laptops and locations.
so I've got a transactions table and I was just going to relate the primary ID from the main table to the transactions and update the location to the transactions table.... it was the best way I thought to do it?

 

[vbaInet]

I don't understand your business logic and I don't know your table relationships so I can't comment on your current way of doing things. If you want someone to help you look at your relationships and tables you can always create a new thread explaining what your db is about and how the tables relate to each. I'm sure someone will pick it up and advise.

In the meantime, have a go with what I advised.

 

[token_remedie]

wouldn't my query have to be false? because I only want to change locations which are different.

If Not (r.EOF And r.BOF) Then
r.MoveFirst
Do Until r.EOF = True
If DCount("*", "[master without matching redisttable]", "'Expr1 = 0'") Then

r.MoveNext

Else
MsgBox "location change"

End If

Loop
End If

working with:

SELECT Master.UID, Master.[Asset Number], Master.[Serial No#], Master.LOCATION, Master.F10, redisttable.[NEW LOCATION], [Master].[Location]=[redisttable].[new Location] AS Expr1
FROM Master LEFT JOIN redisttable ON (Master.[serial no#] = redisttable.[serial no#]) AND (Master.[Asset Number] = redisttable.[Asset Number])
WHERE (((redisttable.[Asset Number]) Is Not Null) AND ((redisttable.[serial no#]) Is Not Null) AND (([Master].[Location]=[redisttable].[new Location])=False));

 

[vbaInet]

From what I explained, there's no looping or recordset involved.

The condition I put there is <> which translates to Not Equal to. So if both Locations are Not The Same that means it's True.

 

[token_remedie]

ohhhhhhh my bad I've been working 12 hours a day on this (cos im learning it takes me longer to figure stuff out) so I'm extermely tired at this point but I get what you're saying now

 

[vbaInet]

Your condition is fine too. You put Equal To and False. It's essentially the same as Not Equal To and True.

The query you have can now be used in an UPDATE query to update the relevant fields.

 

[token_remedie]

so... something like:

If DCount("*", "[master without matching redisttable]", "Expr1 = 0") Then
DoCmd.RunSQL "UPDATE Master, Transactions INNER JOIN redisttable ON Transactions.[Asset Number] = redisttable.[Asset Number] SET Master.[Asset Number] = [Transactions].[Asset Number], Master.LOCATION = [Transactions].[Location];"

Sorry if I'm not understanding correctly

 

[vbaInet]

Before we go any further, does this query return the records you expect?

SELECT Master.UID, Master.[Asset Number], Master.[Serial No#], Master.LOCATION, Master.F10, redisttable.[NEW LOCATION], [Master].[Location]=[redisttable].[new Location] AS Expr1
FROM Master LEFT JOIN redisttable ON (Master.[serial no#] = redisttable.[serial no#]) AND (Master.[Asset Number] = redisttable.[Asset Number])
WHERE (((redisttable.[Asset Number]) Is Not Null) AND ((redisttable.[serial no#]) Is Not Null) AND (([Master].[Location]=[redisttable].[new Location])=False));

 

[token_remedie]

sure does
it shows matching asset numbers and serial numbers with different locations.

 

[vbaInet]

Nice

So what you want to do is save that as a query, create a new UPDATE query and join that to your query above via the relevant fields. Then when necessary, fire off the UPDATE query. Is that clearer?

 

[token_remedie]

*high five* I'll get on that straight away if you were here I'd buy you a coffee

 

[vbaInet]

     | | | |
    \ -|- |
     ----

That's supposed to be my hand :S

 

[token_remedie]

hahaha that made me laugh so hard I almost spat coffee on my monitor!

how's this looking?

INSERT INTO Transactions ( id, LOCATION )
SELECT [Master Without Matching redisttable].UID, [Master Without Matching redisttable].LOCATION
FROM [Master Without Matching redisttable]
WHERE (((Exists (SELECT * from [Master Without Matching redisttable]))<>False));

The result count is the same in both queries so I'm guessing I got it right?

 

[token_remedie]

it wont add them due to key violations, is that because im trying to copy the primary key from master into transactions? To keep space down I'm just copying two fields.