Using Current Fields to populate another Table 1

[Gortok]

I inherited a legacy table that utilizes "Yes/No" Values with the field names being the actual value that I want. For example. If The field name is RedCar, and the Value is 'Yes' or 'No', depending on whether or not the person has a red car. If the field Name is 'BlueCar', then the value of the field is 'No' or 'Yes', depending on if they have a blue car. I don't want to get into the issues of converting this table into something meaningful, simply because the solution I need for the problem is to be implemented soon. I can spend all of the time in the world that I'd like on redesigning the legacy tables. Philosophical issues aside, here's what I need to do with the 'RedCar=Yes' information. This information needs to go into another table (programmatically, obviously) that utilizes the following: CarType = RedCar, or CarType = BlueCar.

As far as simple SQL goes, I was thinking some combination of an update and Make table query, but I can't seem to get the logic right, and I don't know why. Hell, even if I could convert those values into a temporary intermediate table, it would help greatly. I was also thinking about VBA, but the language itself is new to me, as in I have less than 5 hours practical experience with it. (I do have the O'Reilly VB and VBA in a nutshell, as well as the "Access Hacks" book by O'Reilly.

Any ideas would be greatly appreciated...

 

[Zion7]

Do you need to create the table VBA, or can that be done by hand? If Not by hand,

Dim strPath As String
strPath = CurrentProject.FullName

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, _
acTable, "tblCars", "tblCarsII", True



As far as inserting DATA goes,
CurrentProject.Connection.Execute _
"INSERT INTO tblCarsII SELECT * FROM tblCars " & _
" WHERE CarType ='Red Car' AND chkRed = True"

etc...

 

[Gortok]

Greetings again. I was a bit vague earlier, and I realize now it's because I don't really go into what was going on. I've since sat down and read more of Access Hacks and VB and VBA in a nutshell, and have come back with a more defined problem. My problem is now is that due to my lack of coding experience in Access VBA, I don't feel like I am correctly identifying the queries I want data from. Any help in this would be appreciated, and I have enclosed the currently written code.

' - Pseudo Code for the 'open', I still haven't put the actual VBA recordset opn stuff in.  ''That'll be next

Open PeopleOnLeaveToday Query
'  
'The first Do loop checks the Social from one field against the last 4 field of another 'query (since someone thought it best not to include full socials in the leave table), it 'also checks the first 3 letters of the last name with the last 4 of the social to acheive 'apretty accurate match of the name.  This should alleviate any issues with accidentally 'modifying a record that doesn't match.

Do While [PeopleOnLeaveToday] NOT EOF   
If [CurrentAcctQuery.Right([SSAN], 4)] = [People On Leave Today.SSAN] AND _ " [CurrentAcctQuery.Left([NAME], 3)] = [People On Leave Today.NAME]  THEN

'This is where the meat and potatoes of the function exist.  The Do While loop Below first 'determines that we haven't reached the end of the query yet.  Once it knows that, it 'iterates through the fields to see whether or not any of them are true.  If it finds a '"True" Field, it records the name of that field and passes that value into the 'status' 'field of Accountability table.  Boy is that a mouthful.
 
  
  Do While [PeopleOnLeaveToday] NOT EOF
	IF [peopleOnLeaveToday.LV] = Yes Then
		[Accountability Table.Status] = "LV"
	   MoveNext                               ' Move to next record in Recordset
	   Exit Do                                ' Go back to top Do.While Statement
	   
	  End If
	Else	
	IF [peopleOnLeaveToday.CN LV] = Then
		[Accountability Table.Status] = "CN LV"
	   MoveNext
	   Exit Do 
	  End If
	Else
	IF [peopleOnLeaveToday.TDY (MED)] = Yes Then
		[Accountability Table.Status] = "TDY"
	   MoveNext
	   Exit Do 
	  End if

	IF [peopleOnLeaveToday.TDY (NON-MED)] = Yes Then
		[Accountability Table.Status] = "TDY"
	   MoveNext 
	   Exit Do 
	  End if
      Loop 	
 Else
MoveNext
Loop

 

[PHV]

You may consider a normalization union query.
A starting point (SQL code):
SELECT SSAN, NAME, 'LV' AS Status FROM peopleOnLeaveToday WHERE LV = True
UNION SELECT SSAN, NAME, 'CN LV' FROM peopleOnLeaveToday WHERE [CN LV] = True
UNION SELECT SSAN, NAME, 'TDY' FROM peopleOnLeaveToday WHERE [TDY (MED)] = True OR [TDY (NON-MED)] = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886