ReStructure Delivery Service
ReStructure Delivery Service
I was asked to repair a db for a delivery service which crashed. VBA appeared to be the only item affected (mostly mumbo jumbo), 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 Xact in the details table. This field shows where the item was picked up, delivered to, and wether or not a round trip 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 Union Query to get around this, however I can't help but believe there's a better way to structure this!
Any Ideas would be appreciated ...
See Ya! . . . . . .
Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading!
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading!
RE: ReStructure Delivery Service
HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work"
RE: ReStructure Delivery Service
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
RE: ReStructure Delivery Service
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 UnionID contains -1 for account names and the ContactID for contact names. The end result is that it takes two queries (one for account, one for contact) to retrieve the address info. The -1 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! . . . . . .
![[thumbsup2] thumbsup2](https://www.tipmaster.com/images/thumbsup2.gif)
Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading!
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading!
RE: ReStructure Delivery Service
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! . . . . . .
![[thumbsup2] thumbsup2](https://www.tipmaster.com/images/thumbsup2.gif)
Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading!
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading!
RE: ReStructure Delivery Service
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"
RE: ReStructure Delivery Service
Thanks again.
All is fine. As I said I was just trying to get around the union query ... forced by the table structure. There satisified it works as before.
See Ya! . . . . . .
![[thumbsup2] thumbsup2](https://www.tipmaster.com/images/thumbsup2.gif)
Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? Worthy Reading!
Also FAQ181-2886: How can I maximize my chances of getting an answer? Worthy Reading!