How to deal with a miscellaneous line (expense) Order details ?- Access 2016 (1 Viewer)

RickHunter84

Registered User.
Local time
Today, 01:31
Joined
Dec 28, 2019
Messages
85
Hello all,

I hope everyone is doing fine.

I have an open question regarding orderTbl and OrderDetailsTbl. The tblOrder is pretty straight forward with the usual requirements, Supplier name, order date, etc. the table has an Autonumber as well. The OrderDetailsTbl table (connected with one to many relationship to tblOrder) feeds its Product items # (connected with a one to many relationship) from a tblProduct. The items required to add information in the order details are the following:

OrderDetailsID (Autonumber)
OrderTblID_FK (connected via relationship to OrderTbl)
ProductID_FK (connected via relationship to tblProductl
Description
Qty
Cond_FK (connected via relationship to tblCondition)
Cost
LineStatus_FK connected via relationship to tblLineStatus)
LineNote

The table above works fine, but when an item such as Expedite fee needs to be added, it doesn't meet the standard requested information from OrderDetailsTbl - This item in theory could be input as as ProductID item in the tblProduct, but since its a miscellaneous charge, its wont have the properties of a regular item, as a normal standard item would, for example: Expedite fee doesn't have a condition.

Here is the open question, how would you handle an item such as the one described above?

Please let me know if further context is required.

thank you in advance.

Rick
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:31
Joined
Oct 29, 2018
Messages
21,358
Hi Rick. I think it's okay to add an Expedite fee record to the OrderDetails table. It's up to you to add/use a FK for it or not. If you do, you could put it in the Products table and so what if it has an empty condition? Just my 2 cents...
 

RickHunter84

Registered User.
Local time
Today, 01:31
Joined
Dec 28, 2019
Messages
85
Hi Rick. I think it's okay to add an Expedite fee record to the OrderDetails table. It's up to you to add/use a FK for it or not. If you do, you could put it in the Products table and so what if it has an empty condition? Just my 2 cents...
Hello DBguy,

Thank you for your prompt response. I tested that option, the only issue I ran into with that is when running the query(tblOrderDetails) to print the order, the expedite line doesn't come up since it doesn't have the condition listed...I had added a "---" in the conditions table to deal with that issue, I was just wondering if its possible to do it with out a workaround.

Rick
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:31
Joined
Oct 29, 2018
Messages
21,358
Hello DBguy,

Thank you for your prompt response. I tested that option, the only issue I ran into with that is when running the query(tblOrderDetails) to print the order, the expedite line doesn't come up since it doesn't have the condition listed...I had added a "---" in the conditions table to deal with that issue, I was just wondering if its possible to do it with out a workaround.

Rick
I don't think it's a workaround if you use an OUTER JOIN in your query. That should be perfectly normal.
 

Users who are viewing this thread

Top Bottom