Need help with Query to put PO number on Detail Rows

[Sue M.]

Hi,

I've imported an invoice from excel into an Access table, tblInvoice. The
table has an ID number column.
I need to figure out how to "copy down" the PO number so it is on every
detail line below it.

Because this is supposed to be a macro to use over and over again, it would
be really great
if it could be automatic. I think it can be done but I don't know how. I
looked into cartesian products
and making some kind of temp table but none of the results of my ideas look
close...

So far my macro empties the tblInvoice table, resets the ID field to 1,
imports the new file and appends it to the tblInvoice table.

For the rows where the PO number is, F1 says "PO No." and F2 has the actual
number I want: "1234A".
I made a new column in tblInvoice, PO_NO to fill in.

SELECT [tblInvoice].ID, [tblInvoice].F1, [tblInvoice].F2
FROM [tblInvoice]
WHERE ((([tblInvoice].F1)="PO NO."));

returns


ID F2
7 4572B
32 3829D
46 5801A
68 4968B
70 5812A

Now I can't figure out in what way to update tblInvoice.PO_NO to "4572B"
where ID is between 7 and 31,
and "3829D" ... between 32 and 45, etc. And "5812A" between 70 and whatever
the last number is.

Please let me know even if this is impossible. Any clue is appreciated!

Thanks!
Sue

 

[KARL DEWEY]

I would go back to the Excel and insert a new column. Column B is now the PO
number column. This assumes you have a single row for column names . Fill new
column starting with second row --
=If(B2="",A1,B2)
Copy column A. Paste Special - Values.

 

[Sue M.]

Karl,

Thank you for your help. I was thinking about it all night and
I think I might have to look into VBA code instead of queries.

I think it would be something like update PO_NO to "0" until F1= "PO No."
and then
start filling PO_NO with the value of ID until F1 = "PO No." again
and switch to that value and keep going until we run out of rows.

Sue

I would go back to the Excel and insert a new column. Column B is now the PO
number column. This assumes you have a single row for column names . Fill new
column starting with second row --
=If(B2="",A1,B2)
Copy column A. Paste Special - Values.

Hi,

I've imported an invoice from excel into an Access table, tblInvoice. The
table has an ID number column.
I need to figure out how to "copy down" the PO number so it is on every
detail line below it.

Because this is supposed to be a macro to use over and over again, it would
be really great
if it could be automatic. I think it can be done but I don't know how. I
looked into cartesian products
and making some kind of temp table but none of the results of my ideas look
close...

So far my macro empties the tblInvoice table, resets the ID field to 1,
imports the new file and appends it to the tblInvoice table.

For the rows where the PO number is, F1 says "PO No." and F2 has the actual
number I want: "1234A".
I made a new column in tblInvoice, PO_NO to fill in.

SELECT [tblInvoice].ID, [tblInvoice].F1, [tblInvoice].F2
FROM [tblInvoice]
WHERE ((([tblInvoice].F1)="PO NO."));

returns


ID F2
7 4572B
32 3829D
46 5801A
68 4968B
70 5812A

Now I can't figure out in what way to update tblInvoice.PO_NO to "4572B"
where ID is between 7 and 31,
and "3829D" ... between 32 and 45, etc. And "5812A" between 70 and whatever
the last number is.

Please let me know even if this is impossible. Any clue is appreciated!

Thanks!
Sue

 

[John Spencer (MVP)]

Probably slow, but the following might work. TEST IT on a copy of your data.

Update tblInvoice as I
SET F2 = DLookup("F2","TblInvoice","ID=" & DMAX("ID","tblInvoice","ID <" & I.ID
& " AND F2 is not null"))
WHERE I.F2 is null


You could test on only one record at a time by adding this to the where clause
to test on only on record.

AND I.ID = 33

Then you can check ID 33 and see what the F2 was set to.

 

[Sue M.]

At first I got a compile error and then I went to Modules>Debug>Compile and
I saw that the error was because of the bad code I was trying to figure out
how to write. : )

I didn't think this could be done with SQL. I've never had any luck with
the DLookup function
before.

I removed that, and this works! Thank you, John Spencer.
It's speedy enough because we are only talking about hundreds of records,
not thousands.

Sue

Probably slow, but the following might work. TEST IT on a copy of your data.

Update tblInvoice as I
SET F2 = DLookup("F2","TblInvoice","ID=" & DMAX("ID","tblInvoice","ID <" & I.ID
& " AND F2 is not null"))
WHERE I.F2 is null


You could test on only one record at a time by adding this to the where clause
to test on only on record.

AND I.ID = 33

Then you can check ID 33 and see what the F2 was set to.

Hi,

I've imported an invoice from excel into an Access table, tblInvoice. The
table has an ID number column.
I need to figure out how to "copy down" the PO number so it is on every
detail line below it.

Because this is supposed to be a macro to use over and over again, it would
be really great
if it could be automatic. I think it can be done but I don't know how. I
looked into cartesian products
and making some kind of temp table but none of the results of my ideas look
close...

So far my macro empties the tblInvoice table, resets the ID field to 1,
imports the new file and appends it to the tblInvoice table.

For the rows where the PO number is, F1 says "PO No." and F2 has the actual
number I want: "1234A".
I made a new column in tblInvoice, PO_NO to fill in.

SELECT [tblInvoice].ID, [tblInvoice].F1, [tblInvoice].F2
FROM [tblInvoice]
WHERE ((([tblInvoice].F1)="PO NO."));

returns

ID F2
7 4572B
32 3829D
46 5801A
68 4968B
70 5812A

Now I can't figure out in what way to update tblInvoice.PO_NO to "4572B"
where ID is between 7 and 31,
and "3829D" ... between 32 and 45, etc. And "5812A" between 70 and whatever
the last number is.

Please let me know even if this is impossible. Any clue is appreciated!

Thanks!
Sue