Open Order Query

[pronet74]

I need to make a query that lists my open order amounts based on the data entry user. However my amounts seem to differ from the ES Open Order reports.

I have my dbo_oeordhdr_sql linked to my dbo_oehdraud_sql table using the tot_sls_amt however like I said it's off. Any of you experts out there that might be able to help me with this?

 

[NEmacGuy]

You have a one to many relationship to deal with. Their will be one OEORDHDR to perhaps many OEHDRAUD records. I would reduce the OEHDRAUD to one in a query and join back to the OEORDHDR.

 

[NEmacGuy]

forgot link should by ord_typ and ord_no

 

[pronet74]

Problem is, how do I do that? In Access using unique records or values still result in duplicate entries, even narrowing it down by ord_type= O and aud_action = A still leaves duplicates.

Seems like some of the duplicates have the status field blank, however not all are like this.

What's weird if I put too many where clauses on the ordaudhdr file I eventually get a ODBC timeout error. The table has close to 400,000 records in it.

 

[pronet74]

One other question. If I just wanted to get the total of open orders what would the formula be?

SELECT Sum(dbo_oeordhdr_sql.tot_sls_amt) AS SumOftot_sls_amt
FROM dbo_oeordhdr_sql;

Shows alot less than what ES is showing.

 

[kscheeler]

it's possible that the tot_sales_amt isn't right in the header and you should bring in the oeordlin_sql table and sum the lines yourself.

Since you are doing this in access, you should first create a query to get just the order number and the data entry user as unique records from the oehdraud_sql table. If that information is in the oeordhdr_sql then you wouldn't need the audit table. Then link that query into the one that gets the total sales amount.

Kevin Scheeler

 

[stravis]

The tot_sales_amt field is not correct until the order is selected for billing or confirm shipped. You need to calculate the order total from the oeordlin_sql table using the following formula (unit_price * qty_to_ship) * ((100 - discount_pct) / 100). This does not account for the misc_amt, frt_amt, or taxes on an order.

Scott Travis

http://www.infospring.net