Need help converting

[PamGnewSQLuser]

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

[nico5038]

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","I D=" & 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)

[PamGnewSQLuser]

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","I D=" & 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","I D=" & 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)

[nico5038]

Hmm, this statement works for me:

UPDATE [tblDailySales]
SET BRANCH = DLOOKUP("field2","[tblDailySales]","ID=" & DMAX("ID","[tblDailySales]","ID <= " & [ID] & " and field1 = 'BRANCH'" ) )

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:

ID	BRANCH	field1	field2	field3	field4	field5
1	ABC	BRANCH	ABC			
2	ABC	blank				
3	ABC	C	1234	cccc	dddd	dddd
4	ABC	I	2345	eeee	ffffffff	ggggg
5	ABC	blank				
6	DEF	BRANCH	DEF			
7	DEF	I	6789	hhhh	iiiii	jjjjj

Nic;o)

[PamGnewSQLuser]

I changed the table name to match your example - but I can not get past this error: SYNTAX ERROR in query expression: (((tblDailySale s.Branch)=DLook Up("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:

UPDATE [tblDailySales]
SET BRANCH = DLOOKUP("field2","[tblDailySales]","ID=" & DMAX("ID","[tblDailySales]","ID <= " & [ID] & " and field1 = 'BRANCH'" ) )

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:

ID	BRANCH	field1	field2	field3	field4	field5
1	ABC	BRANCH	ABC			
2	ABC	blank				
3	ABC	C	1234	cccc	dddd	dddd
4	ABC	I	2345	eeee	ffffffff	ggggg
5	ABC	blank				
6	DEF	BRANCH	DEF			
7	DEF	I	6789	hhhh	iiiii	jjjjj

Nic;o)

[nico5038]

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

Nic;o)

[PamGnewSQLuser]

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 :(