Thanks-a-bunch,
Mo
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Mo-
>
> 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.
