SQL query help...nested join needed?

[DBNovice]

Hello,
I was hoping for advice on a query that I am attempting to write. The manipulations involve changes to the "Locale" field where the "Name" code is the same. I need to be able to have a Locale code from 0001-0005 for each Name code. Where a Locale code is not present, then one will be created and given a Cash amount based on the "0099" code for that particular Name code.

For example, the below sample from my table looks as follows:


Name Locale Cash
100A 0002 $1.23
100A 0003 $5.00
100A 0004 $0.23
100A 0005 $4.98
100A 0099 $2.22
100B 0001 $0.53
100B 0003 $9.01
100B 0004 $3.26
100B 0099 $2.75
100C 0003 $3.32
100C 0004 $8.01
100C 0005 $4.62
100C 0099 $1.05

Below is how I would like my final display to appear with the new data in bold:

Name Locale Cash
100A 0001 $2.22
100A 0002 $1.23
100A 0003 $5.00
100A 0004 $0.23
100A 0005 $4.98
100A 0099 $2.22
100B 0001 $0.53
100B 0002 $2.75
100B 0003 $9.01
100B 0004 $3.26
100B 0005 $2.75
100B 0099 $2.75
100C 0001 $1.05
100C 0002 $1.05
100C 0003 $3.32
100C 0004 $8.01
100C 0005 $4.62
100C 0099 $1.05

As you can see, there are only three distinct Name codes in the table (100A, 100B, 100C). The missing Locale codes are basically being filled in by the data associated with the 0099 Locale code for that particular Name. For example, since Name = 100A was missing Locale = 0001, a new row with these values and the Cash amount for Locale = 0099 will be created.



The queries that I'm working are starting to look like gibberish as my thought process seems to be spirling out of control. I think I should another table containing only a complete Locale column code range (from 0001-0005) to compare against the main table. I keep running into problems, however.

This is something that needs to be done in Access. I would really appreciate any advice or nudge in the right direction. Thank you very much.

[Symlink]

Dim rSet as RecordSet
Dim Fld as Field
Dim DB as Database 

Set DB = CurrentDB()
set rSet = DB.OpenRecordSet("TableName")

While Not rSet.EOF

    For Each Fld in rSet.Fields

         Insert whatver your want to do here

    Next
rSet.MoveNext
Wend

http://social.msdn.microsoft.com/For...for-each-field

I think that might help you figure out how to look for missing values via query and replace them with whatever you want. Hope this helps

[DBNovice]

Dim rSet as RecordSet
Dim Fld as Field
Dim DB as Database 

Set DB = CurrentDB()
set rSet = DB.OpenRecordSet("TableName")

While Not rSet.EOF

    For Each Fld in rSet.Fields

         Insert whatver your want to do here

    Next
rSet.MoveNext
Wend

http://social.msdn.microsoft.com/For...for-each-field

I think that might help you figure out how to look for missing values via query and replace them with whatever you want. Hope this helps

Thanks a lot for the input. I will give it a try. I appreciate it.