Solved

Access - A query needed to keep inventory based on Sales

Posted on 2007-04-07
5
259 Views
Last Modified: 2010-03-20
I can't seem to get this done, am frustrated as heck and would appreciate help...

I have two Access tables that I'm trying to query to get a running inventory and a quick glance of how much has been made on each against the cost of the items.

Table 1 - Named: Inventory
        Fields wanted: ITEMNBR (the Item Number), COST (My Cost) CQTY (How many I have in Stock)

Table 2 - Named: Sales
        Fields wanted: ItemID (the Item Number), Quantity (the quantity sold), PriceEa (price each the customer paid),                          TotalPrice (Quantity x PriceEa)

I want this:

ITEMNBR COST CQTY InStock(Inventory.CQTY - Sales.Quantity), SalesTotal (Sales.PriceEa x Sales.Quantity)


Now, here's the kicker. the Sales.PriceEa is not always the same for each item. And I have multiple lines for items in Inventory.ITEMNBR because my cost changes with each order that I make.

So, here's an example of my Sales Table:

ItemID      PriceEa      
AC2001       2.50
AC2001  0.75
AC2001  3.60

An Example of my Inventory Table:

ITEMNBR      Cost      CQTY
AC2001      1.50      2
AC2001      2.75      2

So I need Access to count the first two sales against the first AC2001 in the table and then, because that one is now out of stock, move to the second AC2001 and count the third sale again that.

I'd like it to look like this:

ITEMNBR      Cost      InStock      SalesTotal
AC2001      1.50      0      3.25
AC2001      2.75      1      3.60

I hope that this makes sense and is possible...

Thanks in advance for your help.
0
Comment
Question by:Mada123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 18871931
hi

in order to achive what you want, you will have to provide some column that will allow to sort the rows
there is no way you can tell which ac2001 item was sold first and which was sold second if you don't have such a column, and it will be impossible to solve your problem
0
 

Author Comment

by:Mada123
ID: 18872286
I'm all for sorting rows and adding columns... I will change whatever is necessary to accomplish this. But I don't know what needs to be done. Can you please assist? Thanks much!
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18872306
i guess the easiest thing to do would be to add a timestamp column (that is date+time)
that will hold for each row the date and time of the insert statement
that way you can keep track of which items were up to sell in every point in time
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question