Here's a detailed run-through:
I have 3 tables regarding the same inventory. I imported them from XL
doc's.
-OldWithPricing_tbl: An old inventory list in which qty's aren't
accurrate, but it's the only list which has pricing.
-"Inv040606_tbl" is an accurate listing of the current inventory.
-"RecentSales_tbl" has an entry for each time a Part# was sold,
including qty-sold, for the last active 6 months.
What we're trying to achieve is:
- a list of the items which are in Inv040606_tbl which have seen
activity (as per RecentSales_tbl)
- a count of how many times each item was sold (count of occurances
in RecentSales_tbl)
- sum of qty sold (RecentSales_tbl) for each item
- the list-price for each item (OldWithPricing_tbl)
THE PROBLEM IS in my CountOfSales and SumOfQtySold.
The numbers returned are way off, but, if you divide SumOfQtySold by
CountOfSales you get the correct qty sold. I have no idea where the
innacurate count is comming from.
Here is my query so far:
SELECT Inv040606_tbl.PartNum, Sum(Inv040606_tbl.Qty) AS SumOfQty1,
Inv040606_tbl.Cond, Count(RecentSales_tbl.PartNum) AS CountOfSales,
Sum(RecentSales_tbl.Qty) AS SumOfQtySold, Avg
(OldWithPricing_tbl.ListEA) AS AvgOfListEA
FROM RecentSales_tbl INNER JOIN (OldWithPricing_tbl INNER JOIN
Inv040606_tbl ON OldWithPricing_tbl.PartNum = Inv040606_tbl.PartNum)
ON RecentSales_tbl.PartNum = Inv040606_tbl.PartNum
GROUP BY Inv040606_tbl.PartNum, Inv040606_tbl.Cond;
The goal is to have a list of the active items, including the extent
of thier activity, as well as the value of each current stock-line
which has had sales in the last 6 months.
Thanks-a-bunch
-Mo
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
