VBA/SQL - Assigning unique values from one table to another

[Heythere82]

Hi

I'm trying to figure out how to copy values from one table to another using VB and SQL. On TableA, I have a list of names. On TableB, I have office room assignments. I want to assign names from TableA to office rooms in TableB (See Below)


TableA

ID Name
1...Bobby
2...Tina
3...Fred
... ...





TableB

ID OfficeRoom Name
1...102A
​

2...102B..........Tina
3...114
4...116
... ...


The three issues making it difficult to solve are:

1) I only want to assign names to records where the Name field is empty in TableB.

2) I dont want already assigned Names to be duplicated/ assigned to another office.

3) I must have Office Rooms assigned in the same order as the list of names in TableA. i.e. Bobby gets the 1st available office on the office list.

I have written a SQL string that Im feeding into a DoCmd. It does autofill names to TableB, but I am lost as how to prevent assigned names from being repeated though. :banghead:





Dim strSQL As String



strSQL = "UPDATE TableB, TableA SET TableB.Name = TableA.Name
WHERE TableB.ID = TableA.ID AND TableB.Name IS NULL"


Debug.Print strSQL

DoCmd.RunSQL strSQL



I know one problem is I have the WHERE condition isolating rows where the ID matches, but Im not sure how else I could maintain the name order from TableA.

I looked up information about the SQL JOIN clauses and they sound promising. Every attempt at adding them to my string failed though.

Any suggestions?

 

[JHB]

..
2) I dont want already assigned Names to be duplicated/ assigned to another office.

In which cases, will that become true when TableB.ID shall be equal to TableA.ID?

 

[Cronk]

Your data base is not normalised ie you don't need to store any name in Table B. A select query on TableA.ID = TableB.IB will show Bobby is assigned to Room 102A and Fred is assigned to Room 114

Your data design won't cater for moving Tina to Room 116

A better design would be
Table A
PersonID
PersonName

TableB
RoomID
RoomName
PersonID

 

[Heythere82]

In which cases, will that become true when TableB.ID shall be equal to TableA.ID?

The WHERE condition causes rooms to only be assigned when both IDs match. This is not what I am trying to do because the both IDs are generally not going to match.

You are correct. The condition (WHERE TableB.ID = TableA.ID) would prevent duplicates from being written. The problem is that it also prevents the name in TableA from being used for the next available office in TableB.(Because the IDs would no longer match)


The condition needs to be removed and replaced with something else. Im just not sure what to replace it with.

 

[Heythere82]

Your data base is not normalised ie you don't need to store any name in Table B. A select query on TableA.ID = TableB.IB will show Bobby is assigned to Room 102A and Fred is assigned to Room 114

Your data design won't cater for moving Tina to Room 116

A better design would be
Table A
PersonID
PersonName

TableB
RoomID
RoomName
PersonID

Thanks for the reply!

Yes, my tables already contain those fields. I was not including them in my post for clarity/simplification purposes.

The issue is adding the personID from TableA to TableB while avoiding adding already assigned personIDs.


I believe I have to write to TableB due to the nature of what Im building.


TableA (name roster) is an imported roster that will change each time the user uploads a new roster. The names, personIDs, and order of rows will be different on each roster.

TableB is used as the recordsource for an Office Assignment form where the user can assign offices manually or by clicking an 'auto assign button'(this is what I'm currently working on). TableB is used as a temporary holding table until the office assignments are finalized and then all assignment information (date, time, assigner, etc.) are logged to the archive/permanent table.


Does this make sense? Any Ideas?

 

[sneuberg]

I agree with Cronk so for the purpose of this post his structure and names have been adopted.

The query to get the people not assigned to a room is just:

SELECT TableA.PersonID, TableA.PersonName, TableB.RoomName
FROM TableA LEFT JOIN TableB ON TableA.[PersonID] = TableB.[PersonID]
WHERE (((TableB.RoomID) Is Null))
ORDER BY TableA.PersonID;

The query to get the free rooms is

SELECT TableB.RoomID, TableB.RoomName, TableB.PersonID
FROM TableB
WHERE (((TableB.PersonID) Is Null))
ORDER BY TableB.RoomID;

If someone can write a query to merge the two I'd really like to see it as it has me stumped. But you can do the assignment brute force in VBA with something like:

Private Sub Update_Click()

Dim rsNotAssigned As DAO.Recordset
Dim rsFreeSpaces As DAO.Recordset
Set rsNotAssigned = CurrentDb.OpenRecordset("qryNotAssigned")
Set rsFreeSpaces = CurrentDb.OpenRecordset("qryFreeOfficeRooms")
Do While Not (rsNotAssigned.EOF Or rsFreeSpaces.EOF)
    rsFreeSpaces.Edit
    rsFreeSpaces!PersonID = rsNotAssigned!PersonID
    rsFreeSpaces.Update
    rsFreeSpaces.MoveNext
    rsNotAssigned.MoveNext
Loop
    
End Sub

This code is run from the Update button on the form in the attached database.

You might note that I didn't use autonumbers in the tables. An autonumber wouldn't be good here as it controls the sort and you can't modify an autonumber.