×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ReStructure Delivery Service

thread700-1667273

ReStructure Delivery Service

ReStructure Delivery Service

(OP)
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 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! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

RE: ReStructure Delivery Service

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"

RE: ReStructure Delivery Service

Quote:


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


   

RE: ReStructure Delivery Service

(OP)
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 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! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

RE: ReStructure Delivery Service

(OP)
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: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

RE: ReStructure Delivery Service

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close