Need Help With Module/VBA

[Sausagefingers]

Hi,
New here, first post! I need to find a way using a module/VBA or even a macro which will 'flag' duplicates in a sequence of numbers. For example, I have a list of delivery destinations in a table. Each delivery is unique but some share the same destination. Because of this they are assigned the same number (delivery sequence): 1,2,3,4,5,5,6,7,8,9,10. This allows me to identify that 2 unique deliveries share the same ETA (exampled by the duplicate number 5 above).

Delivery 5 needs to be treated as one destination, although there are actually 2 deliveries.

How will a module to recognize the duplicate entries and inform the database user that ETA is only required for one delivery and not two? Using Access 2000.

Thanks for any help you can offer.

 

[OllyK]

Hi,
New here, first post! I need to find a way using a module/VBA or even a macro which will 'flag' duplicates in a sequence of numbers. For example, I have a list of delivery destinations in a table. Each delivery is unique but some share the same destination. Because of this they are assigned the same number (delivery sequence): 1,2,3,4,5,5,6,7,8,9,10. This allows me to identify that 2 unique deliveries share the same ETA (exampled by the duplicate number 5 above).

Delivery 5 needs to be treated as one destination, although there are actually 2 deliveries.

How will a module to recognize the duplicate entries and inform the database user that ETA is only required for one delivery and not two? Using Access 2000.

Thanks for any help you can offer.

Not quite sure how you want this checking to manifest its self, ie a data update or something in the UI.

 

[Sausagefingers]

Not quite sure how you want this checking to manifest its self, ie a data update or something in the UI.

Hi and thanks for your response. This is essentially a transport database. Amongst many other things, it will be logging the arrival and departure times of a fleet of vehicles at destination points on a daily basis. There are 280+ destinations in the current branchList table. These are (numerically) sub-divided into routes (29 so far), each having multiple branches. The branches are then assigned an arrival sequence (per route) as I have inadequately tried to explain in my first post. Where branches share the same destination (as in my example) I would like the database to treat the duplicate numbers in any given sequence as one delivery.

Ideally, the db operator should only have to input the arrival/departure time once for every occurrence of branches that share the same destination (or duplicate sequencial number). I'm having a REAL problem deciding how best to achieve this.

On the input form, the operater tabs through the branches on each route inputting the arr/dep times but currently, there is no implementation which will inform the user that only one Arrival/Departure time is required for multiple branches sharing the same destination. In answer to your question, feedback to the user via the UI is going to be a requirement.

Sorry for the lengthy post here, but I am under a certain amount of 'pressure' to resolve this asap therefore, I'm thinking out loud at the moment!

Once again, thanks for your input.

 

[Sausagefingers]

To better explain the scenario, lets take the first example and call it route_1. In it, there are 11 branches but because branch 'x' and branch 'y' share the same destination there are only 10 'drops' and therefore, only 10 separate arrival and departure times. However, on route_2, there may be 16 branches but only 8 drops. The sequence may look like this: 1,1,1,1,2,3,3,4,4,4,5,6,6,7,7,8. Branch 'a','b','c' and 'd' share destination point 1, 'e' has destination point 2 and so on and so forth.

It looks to me like an if, then, else statement would do the trick but I have no idea how to implement this! My VBA knowledge is very limited

Thanks

 

[Bat17]

I am Not sure that I am visualizing this properly, but, if you are stepping from record to record to add times then you could use a dlookup function to populate the time field if the destination has already been used

Peter

 

[Sausagefingers]

I am Not sure that I am visualizing this properly, but, if you are stepping from record to record to add times then you could use a dlookup function to populate the time field if the destination has already been used

Peter

How do I write a DLookup expression that would check each sequence for duplicates? Given that the duplicate value in route_1 may be 5 but in route_2 the duplicate values may be 1,3,4,6,7 etc?

Forgive my complete lack of knowledge in this area but the permutations must be many.

Thanks

 

[Bat17]

I was assuming that each duplicate check only needs to be made when an entry is made. So when someone enters a location 1, you will check for 1's already entered.

Peter

 

[OllyK]

Going back to basics, I think you potentially have a DB design issue. I'd be looking at having a Delivery table and a Consignment Table, probably with a Packages table, so for any given delivery location you can have multiple consignments each with 1 or more packages. You then store arrival time in the DeliveryLocation table. I'd also be tempted to have a Master Address table so you just post the primary key in to the Delivery Table.

[Address]1-M[Delivery]1-M[Consignment]1-M[Package]

 

[Sausagefingers]

Going back to basics, I think you potentially have a DB design issue.

Yep! I guess so...

I have been chewing this over for too long without a solution! I knew this was inevitable. Back to the drawing board for me then!

Still...It's a learning curve. I'll post my solution just for those of you that are curious enough to see someones brain sizzling