Matching Values without Duplicates

[ExoDus84]

Hey guys and gals, I've got an issue that perhaps some of you can point me in the right direction for. I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:

Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3

I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.

Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?

I'm sure there's a better way to do this in VB, but I'm still new to VBA and I'm lost.

 

[namliam]

Yeap VB is your way to go...
Start by looking into the Do While / loop
As well as DAO.Recordset and the way to use them to run queries.

 

[ExoDus84]

Can you be a little more specific? I'm not that familiar with VBA quite yet. I'm not sure where I would begin with this.

 

[namliam]

DAO.Recordset: http://msdn.microsoft.com/en-us/library/office/ff197799.aspx

Which also holds quite some samples of things you can do with it.

 

[ExoDus84]

Hey everyone,

I still haven't found a valid solution to this. I tried using a sql update query, but it doesn't work, since the join is not a direct one to one. I may have 1000 open locations with profile name A, but what if I need to update only 50 part numbers with unique locations?

I'm tryinig to use the recordset objects to manipulate it via code, but I can't find the right code.

What I need my code to do is find out how many records are in "test_locs", grab exactly that many locations from the "test_open_locations" table, and update them in the "test_locs" table.

Here's what I have:

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordset("test_open_locations", dbOpenDynaset)
Set rst2 = db.OpenRecordset("test_locs", dbOpenDynaset)
 
Do While Not rst.EOF
  With rst2
     .Edit
      rst2![Location] = rst![Location]
     .Update
   End With
   rst.MoveNext
 Loop
 
MsgBox ("Done")
 
rst2.Close
rst.Close
Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

The code above grabs the last location value in rst, and appends it to the last record in rst2. I want it to grab unique values for each record in rst2.

I tried using the Dcount function to count open records, and then use a For/Next loop to only grab as many as I needed, but it didn't work. I'm not sure if my syntax was wrong.

Can someone give me a few pointers?

 

[namliam]

Any particular reason you would have the 1000 "dummy" records to start with?
If you didnt have the dummy records in place you could "simply" append the 50 locations to your final table instead of this complicated updating stuff

rst and rst2 are kind off "odd" variables, can advice you to use "usefull" names for variables...

Assuming you want to update the records 1:1 from one table to another and the location of the destination table needs to be empty for it to recieve the new location from the update process....

With above 2 things in mind.... something like:

Dim rstTL As DAO.Recordset
Dim rstOL As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rstTL = db.OpenRecordset("test_open_locations", dbOpenDynaset)
Set rstOL = db.OpenRecordset("Select * from test_locs where location is null", dbOpenDynaset)
 
Do While Not rstTL.EOF and not rstOL.eof
  With rstOL
     .Edit
      rstOL![Location] = rstTL![Location]
     .Update
   End With
   rstTL.MoveNext
   rstOL.movenext
Loop
 
MsgBox ("Done")
 
rstOL.Close
rstTL.Close
Set rstTL = Nothing
Set rstOL = Nothing
Set db = Nothing

I hope this helps you along

 

[ExoDus84]

Thank you, Namliam. I'll give this a try and see what happens.

 

[jdraw]

Exodus,

Can you show us a jpg of your relationships window? You may have to zip the file.

Or could you describe your table structures:
table name
field name
field data type
any keys or indexes.

 

[ExoDus84]

Exodus,

Can you show us a jpg of your relationships window? You may have to zip the file.

Or could you describe your table structures:
table name
field name
field data type
any keys or indexes.

The field types for both the source and destination files are equivalent in terms of data types. I don't have keys on them at the moment. Naimlam's example above is kind of what I'm looking for, although I need to match up a field from both tables and pull in locations that match that equal field.

Going back to my example in the first post, I have two tables:

1. Locations, that looks like this:

Profile 1 - Location 1
Profile 1 - Location 2
Profile 2 - Location 1
Profile 2 - Location 2 (etc)

2. Parts that need a location based on table 1.
Part 1 - Profile 1
Part 2 - Profile 1
Part 3 - Profile 2
Part 4 - Profile 2

Table 1 will have a list of profiles and available locations. I need to match the profile types in both tables, and bring in unique location values into table 2.

I modified his code from above, and it works, to a degree, with the exception that this code is pulling in the same location value for each profile type, instead of stepping through and finding a unique location each time.

Dim rstTL As DAO.Recordset
Dim rstOL As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rstTL = db.OpenRecordset("test_open_locations", dbOpenDynaset)
Set rstOL = db.OpenRecordset("Select * from test_locs where location is null", dbOpenDynaset)
 
Do While Not rstTL.EOF And Not rstOL.EOF
rstTL.FindFirst "[Class] = '" & rstOL![RCLASS] & "'"
   With rstOL
     .Edit
      rstOL![Location] = rstTL![Location]
     .Update
   End With
      rstTL.FindNext "[Class] = '" & rstOL![RCLASS] & "'"
      rstOL.MoveNext
 Loop
 
MsgBox ("Done")
 
rstOL.Close
rstTL.Close
Set rstTL = Nothing
Set rstOL = Nothing
Set db = Nothing

With this code, the output is:

Profile 1 - Location 1
Profile 1 - Location 1
Profile 2 - Location 2

etc. :banghead: