Queries needed for selective table joining.

[bunglefoot]

--I think I posted this in the wrong forum earlier, so I am reposting it here.

Hey, posted a while back on this subject and got some good responses but nothing I could get working in the desired manner. I have more information now.

I have a table with four fields coming in from a device (called deviceTable). The table succesfully enters MS Access with no difficulties.

It has four fields, field1 is text, field2 is text from a drop-down control, field3 is a date, and field4 is text from another drop-down control. There is a similar table in existance on the PC (pcTable) with all of these fields as well as a numerical field, field5. Records in these tables and between the tables are considered identical IFF both field1 AND field2 are identical.

What I need to do, in order:

1: Take deviceTable, and merge it with pcTable such that any record in deviceTable that does not exist in pcTable is created with field5 initialized to one. If the record in deviceTable matches a record on the PC (field1 and field2 match a counterpart record's field1 and field2 on pcTable) then the pcTable's field5 is incremented by one.

2: Print out pcTable, it doesn't really have to be pretty but it should be sorted by field4.

3: Remove any and all records from pcTable that are present in pcTable but not in deviceTable.



This is just using the SQL view of the query designer in MS Access 2002. Ideally, all three steps would occur at once with minimal input from the user.

Thanks for your help!

 

[PHV]

And what have you so far ?

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

 

[willir]

bunglefoot

An approach, without the details would be to use some code. Perhaps something under a command button...

Dim strQ as String, strWhere as String, strSQL as String

strQ = Chr$(34)
strWhere = "[field1 ] = & strQ & Me.field1 & strQ & " and [field2] = " & strQ & Me.field1 & strQ

If (DLookup("[field1]", "pcTable", strWhere) Then
   'Found a value, increment counter, field5
   strSQL = "UPDATE pcTable Set field5 = field5 + 1 " & strWhere
Else
   'Need new record
   strSQL = "INSERT INTO pcTable (field1, field2, field3, field 4) " _
   & "SELECT field1, field2, field3, field4 WHERE " & strWhere
End If

DoCmd.RunSQL strSQL

Pretty crude, but hopefully you get the idea...

Richard