ReStructure Delivery Service

[TheAceMan1]

Howdy All ... Hope everyone is well.

I was asked to repair a db for a delivery service which crashed. VBA appeared to be the only item affected (mostly [red]mumbo jumbo[/red]), aside from rebuilding two forms. I've been successful in patching so they can keep working, however the relationships keep bothering me. See the link below:
Delivery Relationships

Prime is the transaction field [blue]Xact[/blue] in the details table. This field shows where the item was [blue]picked up[/blue], [blue]delivered to[/blue], and wether or not a [blue]round trip[/blue] was performed. This is all concatenated in code. Below is a sample of what it looks like in the form:
Pickup/Delivery Form View

Since items can be pickedup from or delivered to the account owner there's a problem in querying service between account owners and contacts of those owners. Currently I used a [blue]Union Query[/blue] to get around this, however [blue]I can't help but believe there's a better way to structure this![/blue]

Any Ideas would be appreciated ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Without rebuilding the entire app they work but the forms are ugly. Personally i like to create the relationships at run time when required as much as possible. Changing them now would probably just open a Pandora's box. You never know how they will effect the existing queries, code etc. Good luck with that one Ace

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

Since items can be pickedup from or delivered to the account owner there's a problem in querying service between account owners and contacts of those owners

I am not sure if I understand the question, so I am guessing. But I have never seen an AceMan question so I figured I have to try. But my interpretation is that the business logic is different then what is modeled. It seems to me that you are saying you want contact information tracked at the Pickup and delivery, but it is modeled to the account only. I would think a delivery contact table is needed so you can assign 1 or more contacts to the delivery.

tblContactDelivery
contactType( Pickup, Delivery, Both)
contactID_FK
detID_FK

 

[TheAceMan1]

MajP . . .

I'll be more clear. 1st, everything is fine. The db works just like they want.

I went on alert when designing a small form to allow the users to simply select the PickUp/Deliver names from comboboxes (see the form below) ...
Transaction Editor
... however I was forced to use a union query so I could include both Account & Contact names in the same combo listing. In this query a field called [blue]UnionID[/blue] contains [blue]-1[/blue] for account names and the [blue]ContactID[/blue] for contact names. The end result is that it takes two queries (one for account, one for contact) to retrieve the address info. The [blue]-1[/blue] lets me know which query/SQL to use.

I was just looking for a way to circumvent the union query. At this point it doesn't look possible.

In any case ... thanks for the input.


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Howdy MazeWorX . . .

Be aware ... all forms & reports were designed by the staff of this company, somewhere around 2000. I have to admit I'm not too hot about the aesthetics, but they know what they want and they know what their doing.

Thanks for the input . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Ironically enough so do I (work for a delivery company) but we only have 11,000 employees

Hope it works out for you

Regards M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

MazeWorX . . .

Thanks again.

All is fine. As I said I was just trying to get around the [blue]union query[/blue] ... forced by the table structure. There satisified it works as before.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]