Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ReStructure Delivery Service

Status
Not open for further replies.
TheAceMan1

TheAceMan1

Programmer
Sep 23, 2003
11,174
US
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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
Click to expand...

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


 
Upvote 0 Downvote
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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

monkeysee
  • Locked
  • Question
Billing Services Tables, set up and relationships
Replies
1
Views
111
ThomasLafferty
ThomasLafferty
Werneck
  • Locked
  • Question
How to structure tables to allocate transport services?
Replies
2
Views
105
Werneck
Werneck

Part and Inventory Search

Sponsor

Back
Top