No surprise. Your OldWithPricing table probably has one row per PartNum.
Ditto Inv040606. But RecentSales has one row per sale per PartNum. So, if
you join Inv040606 with RecentSales, you'll get duplicate inventory data -
one row per row that matches in RecentSales. Like this:
PartNum Inventory Qty Date Sold Sale Qty
12345 155 04/01/2006 10
12345 155 04/10/2006 5
12345 155 04/25/2006 8
What you need to do is Sum the data from RecentSales first, then use that in
a simple Join with the other two tables to get the correct numbers. You
might want to use an outer join from Inv040606 to the sum of sales in case
some products had no sale. That will ensure you get all inventory part
numbers and any matching total sales.
Let me know if you need help building the SQL.
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of Mo
Sent: Saturday, May 06, 2006 2:32 AM
To: [email protected]
Subject: [ms_access] Where am I going wrong?
I'm a novice. Any help is appreciated.
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
Yahoo! Groups Links
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.
