How do I make this Query?

[Kira]

How do you make a query that takes data from one field and get it to subtract from the correlating field in another table?

For instance: I have 43 Gears. The next day 4 of them are used for repairs. So I have one table "Item Use" with Gears-4 and the original with Gears-43. I need the 4 to be subtracted from the 43.

 

[ajetrumpet]

this type of stuff should be done in Excel. Access is not the best thing to use. If your database is more complicated, you should give more detailed information in order to get help.

 

[neileg]

The best way to hold inventory data is as a series of transactions in and out all in the same table. You then sum them to get the current value. This has often been discussed in these forums.

 

[Big Pat]

To elaborate on what neileg said, the way I have done something similar is....

Two tables, one called "Items" (for instance) with code, description, size, price etc. the other called "Transactions" with ItemCode, TransactionType, TransactionQty, TransactionDate etc.

You would then populate the Transactions table with data like:

AA1 Start 50 01/01/08
AA1 Use -5 02/01/08
AA1 Purchase 20 03/01/08
AA1 Use -10 04/01/08
etc.

Using data like this, you can them sum all the transactions for a certain code and work out that you should have 55 in stock.

Does this make sense?

 

[Kira]

Not really sure what the AA1 signifies. To be honest I would probably need a good tutorial for something like this. All of my access knowledge thus far has come mostly from messing around with it and I am sure I have several misconceptions on how things work. So would I need to make a query? I have no real idea how a query works.

here is a link to a download of my database: http://www.access-programmers.co.uk/forums/showthread.php?t=152582


Thanks for the replies.

 

[neileg]

Big Pat has used AA1 as a possible product code.

Your tables has repeating fields for the days of the week. This is not a good design. Each day sould be an individual record in a second table.

Search in these forums for lots of help on inventory and stock control.

 

[pbaldy]

I take it you didn't like the link I gave you?

http://www.access-programmers.co.uk/forums/showthread.php?t=152576

 

[Kira]

I looked at it, but I am just not really sure how to do that. I guess I will just have do a lot of researching around this site. I only have a few weeks to finish this project though and I am kind of nervous. I had NO previous access experience and have been given the task of creating a database when no one here knows anything about access either. Uhg.

Thank you for the replies. I will try to use them to find out what I need.

 

[Big Pat]

AA1 is simply an example of a product code. It could be anything at all, like in your case "Gear 1", "Gear 2" etc. What you would be doing is logging in this table each time your stock moved. You may not even need the Items table if you're not bothered about needing to store information about what "Gear 1" actually is.

To give another example, the individual records in your table could look like this (minus the dots which I'm using for spacing). Each "column" corresponds to a field in your table.

Gear 1....start.....50....1st Jan
Gear 2....start.....30...2nd Jan
Gear 1....used....-20...5th Jan
Gear 1....used....-10...8th Jan
Gear 2....used..... -5...16th Jan
Gear 1...bought...20....1st Feb
Gear 2...bought...10....1st Feb
Gear 1....used.....-20...2nd Feb
etc.
etc.

With data like this, you can construct a query that will SUM all the values (including the negative ones) for each of your "Gears" and the query should tell you how many are left e.g.

GearNo.......QtyInStock
-----------------------
Gear 1...........20
Gear 2...........35


If you are a novice at Access (and we've ALL been there, some like me pretty recently), then you really ought to read up a little on table design. Start with the basics, understand what a table is and what kinds of data it can hold.

Then, as neileg says, search this forum for examples of inventory and stock control databases.

There is so much expertise available here. I've learned TONS on this forum and I keep coming back for more.

 

[Kira]

Thank you Big Pat I found your reply to be very informative.

 

[pbaldy]

My point was that as the link noted, trying to store quantity on hand can be difficult. You have to have complete control of anyplace where users might add/modify/delete records. It's much simpler to calculate it from transactions, as Big Pat's example shows (love the signature Pat!). Can we assume this is a school project? Tell your teacher that it's against the rules of normalization to store calculated values.