filling up cells in a pattern of data

[vasu1211]

I have a table which has

OriginSt OCity Dcity Dstate NoOfShipments
A B C D 24
A B C D ?
E F G H 34
E F G H ?

There is a pattern of data here. Four fields in first two rows are same. i have to fill up the ?s in the last column for alternate rows- These ?s are going to be same as in the previous rows. example 24 in the second row and 34 in fourth row. This pattern of data continues through out the table which has hundreds of records.

How do I do this by a query or a table feature.I can do this manually by copying and pasting but it would take a lot of time and effort.

Any help would be greatly appreciated

 

[dhookom]

I would probably create a make table query that groups by all four fields and finds the max of the NoOfShipments. Then set the primary key of the made table to the four fields.

You can then join this new table to your existing table to create an update query that updates NoOfShipments with MaxOfNoOfShipments where NoOfShipments is null.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vasu1211]

Thank you so much. it helped me a lot