By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Need help converting

P: 4
I have a table with 3 different options in field 1 - if blank I need to skip - if equal to BRANCH I need to move value in field 2 to field 3 until EQUAL to BRANCH again - if any other value move fields
example of table
field 1 field 2 field 3 field 4 field 5
BRANCH ABC
blank
C 1234 cccc dddd dddd
I 2345 eeee ffffffff ggggg
blank
BRANCH DEF
I 6789 hhhh iiiii jjjjj

for these 7 records - I only need 3 - they should be:
ABC 1234 cccc dddd dddd
ABC 2345 eeee ffffffff ggggg
DEF 6789 hhhh iiiii jjjjj

OK to add a new column

any ideas?
Thanks for the help
Aug 24 '08 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
Nice problem.
You can solve this using queries when you start with adding an autonumber as the first ID and an empty temp field BRANCH like:

ID BRANCH field 1 field 2 field 3 field 4 field 5
1 "Empty" BRANCH ABC
2 "Empty" blank
3 "Empty" C 1234 cccc dddd dddd
4 "Empty" I 2345 eeee ffffffff ggggg
5 "Empty" blank
6 "Empty" BRANCH DEF
7 "Empty" I 6789 hhhh iiiii jjjjj
etc.

We now can fill the BRANCH field like:
UPDATE tblX SET BRANCH = DLOOKUP("field2","tblX","ID=" & DMAX("ID","tblX","ID < " & [ID] & " and field1 = 'BRANCH'" ) )

This will fill all "Empty" fields with the appropriate BRANCH value.
I guess you can write finally the query to drop the BRANCH and blank rows...

Just to be safe, first make a backup of your table before performing an update !

Nic;o)
Aug 24 '08 #2

P: 4
Thanks for the help - but as my ID says - I am new!!! :(

First tblX - this would be the full table name - correct?

Here is what I have:
UPDATE [Daily Sales - Excel - Step 1]
SET BRANCH = DLOOKUP("Order #","tblX","ID=" & DMAX("ID","tblX","ID < " & [ID] & " and test = 'BRANCH'" ) )
FROM [Daily Sales - Excel - Step 1];

I also tried:
UPDATE [Daily Sales - Excel - Step 1]
SET BRANCH = DLOOKUP("Order #","[Daily Sales - Excel - Step 1]","ID=" & DMAX("ID","[Daily Sales - Excel - Step 1]","ID < " & [ID] & " and test = 'BRANCH'" ) )
FROM [Daily Sales - Excel - Step 1];

I am getting syntax errors on the DLOOKUP - and I am not finding that command to check it

Do you have time to help?


Nice problem.
You can solve this using queries when you start with adding an autonumber as the first ID and an empty temp field BRANCH like:

ID BRANCH field 1 field 2 field 3 field 4 field 5
1 "Empty" BRANCH ABC
2 "Empty" blank
3 "Empty" C 1234 cccc dddd dddd
4 "Empty" I 2345 eeee ffffffff ggggg
5 "Empty" blank
6 "Empty" BRANCH DEF
7 "Empty" I 6789 hhhh iiiii jjjjj
etc.

We now can fill the BRANCH field like:
UPDATE tblX SET BRANCH = DLOOKUP("field2","tblX","ID=" & DMAX("ID","tblX","ID < " & [ID] & " and field1 = 'BRANCH'" ) )

This will fill all "Empty" fields with the appropriate BRANCH value.
I guess you can write finally the query to drop the BRANCH and blank rows...

Just to be safe, first make a backup of your table before performing an update !

Nic;o)
Aug 24 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Hmm, this statement works for me:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblDailySales]
  2. SET BRANCH = DLOOKUP("field2","[tblDailySales]","ID=" & DMAX("ID","[tblDailySales]","ID <= " & [ID] & " and field1 = 'BRANCH'" ) )
  3.  
With a table named tblDailySales (best not to use spaces in tablenames) and just by adding "ID" autonumber and "BRANCH" as the first two fields in a table with the original fields named field1, field2, etc.

Your sample data gave:
Expand|Select|Wrap|Line Numbers
  1. ID    BRANCH    field1    field2    field3    field4    field5
  2. 1    ABC    BRANCH    ABC            
  3. 2    ABC    blank                
  4. 3    ABC    C    1234    cccc    dddd    dddd
  5. 4    ABC    I    2345    eeee    ffffffff    ggggg
  6. 5    ABC    blank                
  7. 6    DEF    BRANCH    DEF            
  8. 7    DEF    I    6789    hhhh    iiiii    jjjjj
  9.  
Nic;o)
Aug 25 '08 #4

P: 4
I changed the table name to match your example - but I can not get past this error: SYNTAX ERROR in query expression: (((tblDailySales.Branch)=DLookUp("Order #","[tblDailySales]","ID=" & DMax("ID","[tblDailySales]","ID <= " & [ID] & " and Test = 'BRANCH'"))) ;

this follows the WHERE - looks like I might be missing a ) - but when I add one (in different stops) then I get an error on Order # - which is field 2

I end the statement there - should there be something else?

Man do I feel dumb - to think I programmed in other languages for 15 years :(


Hmm, this statement works for me:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblDailySales]
  2. SET BRANCH = DLOOKUP("field2","[tblDailySales]","ID=" & DMAX("ID","[tblDailySales]","ID <= " & [ID] & " and field1 = 'BRANCH'" ) )
  3.  
With a table named tblDailySales (best not to use spaces in tablenames) and just by adding "ID" autonumber and "BRANCH" as the first two fields in a table with the original fields named field1, field2, etc.

Your sample data gave:
Expand|Select|Wrap|Line Numbers
  1. ID    BRANCH    field1    field2    field3    field4    field5
  2. 1    ABC    BRANCH    ABC            
  3. 2    ABC    blank                
  4. 3    ABC    C    1234    cccc    dddd    dddd
  5. 4    ABC    I    2345    eeee    ffffffff    ggggg
  6. 5    ABC    blank                
  7. 6    DEF    BRANCH    DEF            
  8. 7    DEF    I    6789    hhhh    iiiii    jjjjj
  9.  
Nic;o)
Aug 26 '08 #5

nico5038
Expert 2.5K+
P: 3,072
OK, I'll add here my sample database so you can check step by step.

Nic;o)
Attached Files
File Type: zip PamGnewSQLuser.zip (10.4 KB, 46 views)
Aug 26 '08 #6

P: 4
OK, I'll add here my sample database so you can check step by step.

Nic;o)
Thanks I got it to work - I was missing a set of brackets :(
Aug 27 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.