MS Access - How do I find and fill in blank record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a situation where I want to find the first record that has a blank
(or null) value (in a certain field), and then copy the value of the record
right above it.

Here is a sample type of data file:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B
1234 C
1234 D
5678 A 05/10/05
5678 B
5678 C
5678 D

In this example, I have a file of sales orders. On the first record for a
customer order, it has the date of the sale for the first item. For all
remaining items sold on that order, the date of sale field is blank (null).
I want to be able to find all of the blank dates, and fill in the date from
the first record, so that I can report on sales by date for selected items.
In this case, I would like the macro to be able to find the first blank
(order 1234, Item B), and copy the date from Item A (05/09/05) into the Date
Sold field for Item B, then C, then D. Then it would find Order 5678, Item
B, and copy the Date Sold (05/10/05) from Item A into the Date Sold field of
Item B, then C, etc.

The completed file would look like:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B 05/09/05
1234 C 05/09/05
1234 D 05/09/05
5678 A 05/10/05
5678 B 05/10/05
5678 C 05/10/05
5678 D 05/10/05


I have a Macro in MS Excel that does this, but some of my files are too big
to fit into a spreadsheet, so I want to be able to perform the same
functionality in Access.

Is there a way to create a macro in Access that would perform this function?

Thanks,

David
 
A good Normalization practice will keep the date sold, in this case, in the
order table, not in the line table, then to create your query/report you join
your lines table to your order header and filter the dates from there, like
this:

select order_line_tab.Order_number,
order_line_tab.Item_Number,
....(other order_line_tab fields like qty),
order_tab.date_sold
from order_tab inner join order_line_tab on
(order_tab.Order_number=order_line_tab.Order_number)
where order_tab.date_sold between [First_Date] and [Last_Date];
 
Another way is to summarize the data with a group by query on the order
number with the date sold. You'll have a list like this:

Order Number Date sold
1234 5/9/05
5678 5/10/05
....

Then you can join the first table to the summarized date table and simply
use an update query to update the null dates to the date sold from the second
table. Simply set up two more queries, toss them in the macro and you're all
set.

RW




jl5000 said:
A good Normalization practice will keep the date sold, in this case, in the
order table, not in the line table, then to create your query/report you join
your lines table to your order header and filter the dates from there, like
this:

select order_line_tab.Order_number,
order_line_tab.Item_Number,
....(other order_line_tab fields like qty),
order_tab.date_sold
from order_tab inner join order_line_tab on
(order_tab.Order_number=order_line_tab.Order_number)
where order_tab.date_sold between [First_Date] and [Last_Date];

--
jl5000
<a href="http://joshdev.com"></a>


David Canfield said:
I have a situation where I want to find the first record that has a blank
(or null) value (in a certain field), and then copy the value of the record
right above it.

Here is a sample type of data file:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B
1234 C
1234 D
5678 A 05/10/05
5678 B
5678 C
5678 D

In this example, I have a file of sales orders. On the first record for a
customer order, it has the date of the sale for the first item. For all
remaining items sold on that order, the date of sale field is blank (null).
I want to be able to find all of the blank dates, and fill in the date from
the first record, so that I can report on sales by date for selected items.
In this case, I would like the macro to be able to find the first blank
(order 1234, Item B), and copy the date from Item A (05/09/05) into the Date
Sold field for Item B, then C, then D. Then it would find Order 5678, Item
B, and copy the Date Sold (05/10/05) from Item A into the Date Sold field of
Item B, then C, etc.

The completed file would look like:

Order number Item Number Date Sold
1234 A 05/09/05
1234 B 05/09/05
1234 C 05/09/05
1234 D 05/09/05
5678 A 05/10/05
5678 B 05/10/05
5678 C 05/10/05
5678 D 05/10/05


I have a Macro in MS Excel that does this, but some of my files are too big
to fit into a spreadsheet, so I want to be able to perform the same
functionality in Access.

Is there a way to create a macro in Access that would perform this function?

Thanks,

David
Click to expand...
Click to expand...
 
Back
Top