Queries :: Stock Database - Calculating Sum In Query?
Apr 17, 2015
I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)
I have 4 Tables:
tblStockList
PartNumber (Primary Key)
Description
Location
StockCheck
StockListUnitPrice
[code]....
I then want to create a query to work out the total stock.
At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)
I then need to combine both these queries to work out the total stock value.
There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.
View Replies
ADVERTISEMENT
Jun 19, 2012
I am trying to calculate the stock level for my spareparts. I am aware of that I am not to update the stock level in my table but calculate the total from the transactions.
View 9 Replies
View Related
Jan 2, 2014
I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.
I have written a query that pulls to fields of data together but cannot get the two to calculate.
Within SQL server management studio I use the following query to give me a numerical response.
SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE
Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?
View 9 Replies
View Related
Jan 2, 2015
I'm creating a sales system that allows customers to place orders using a customer account. when a customer completes an order the number items in the order needs to be subtracted from the stock numbers of each of those items stored in the system.
What I have so far is zipped and attached,
The way my system works should be easy enough to understand, there are four linked tables that store information on customers, products and orders. What I'm really struggling to understand is how to take the values in the records in the Invoice-Product table and take them away from the stock numbers for the products in the products table. But only new orders should take away from the stock number when a new order is placed.
If possible I also need to be able to add stock numbers for example if a delivery comes in, and if it's similar enough could that also be done here?
View 4 Replies
View Related
Jan 21, 2014
I have a problem with calculating the total qty of two fields.
Code:
QtyMapics: Nz([MapicsQty];0)
QtySubComp: Nz([SubComponentQty];0)
TotalQty: [QtyMapics]+[QtySubComp]
When I calculate the values of the fields it pastes the values after each other.For example: if you have the values 1 and 2 (which should become 3) then the result is a value of 12.I've tried changing the function to SUM() but that didn't work.The reason I am using NZ() is due to the fact that some values have a NULL value in one or both of the fields and if NZ is not included it will not calculate anything.
View 4 Replies
View Related
May 12, 2015
I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....
I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.
View 6 Replies
View Related
Apr 23, 2015
I'm working on a utilization database and pulling Excel data from Cognos 8. I use Access formulas to separate hours and minutes because I want to use time (.30, .45 (minutes)) instead of quarterly increments (.25, .50). I've successfully segregated hours, because I also have to turn days into hours. When I try to calculate out a column just for minutes, my formula keeps the number of days and I don't know how to fix it so the column just shows the minutes.
An example is a job has a decimal time of 2.02083333, which when done correctly is 48 hours and 30 minutes. I get the 48 hours correct, but the minutes shows as 2.30 instead of 0.30. That inflates the total job time to 50.30. This is the calculation currently in my database to pull out minutes:
Minutes: Int(([Min]60)24) & "." & Format([Min] Mod 60,"00").
How do I get rid of the '2' in the 2.30?
Access 2010
View 2 Replies
View Related
Nov 3, 2013
I have problem to create Stock query in Access 2013..I want to sum Purchasing numbers base ItemName in Purchasing table by query .. but i found error in summing?
View 4 Replies
View Related
Feb 18, 2014
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
View 3 Replies
View Related
Jun 26, 2013
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code:
SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num]
FROM LIST_WITH_TNC;
Is there a way to make this work?
View 2 Replies
View Related
May 2, 2013
I'm trying to make a query where it can calculate the difference between a table field value and a query field value.
Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value
I need to get the difference between invoice value and total invoice value.
How can I put this into a query (invoice value - total invoice value)?
View 4 Replies
View Related
Oct 2, 2014
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
View 5 Replies
View Related
Feb 18, 2015
I'm making a query that has the following Fields
Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
View 14 Replies
View Related
Jan 21, 2015
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice
01/01/2015 101.45
02/01/2015 104.70
03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
View 3 Replies
View Related
Sep 6, 2007
Hi,
I am creating a database which keeps control of stock. Each item is scanned in with a barcode scanner and given a unique stock number. The Manufacturer, System, Subsystem, Location and Classification is then added.
When a customer phones, and orders a product i.e. a camera, a customer order number can be added to the item in stock. The problem with this is that if the customer is ordering a number of items, and other items are not in stock yet, this item needs to be seen as unavailable. Due to large amounts of stock, i do not want to allocate the customer a specific item with stock number, but just allocate one of the many items of that sort. I do not want to allocate a specific item as when you go to collect the item from the stock room, every item of this type would have to be checked to find the specific stock number, which would be timely and difficult.
I tried using a make table query, which copies the information from the stock table, except the stock number, and so with this list, i will have a list of every item. I can then produce a table which shows me how many of each type of item is available. The problem is, this needs to update every time more stock is added, but i also need to be able to allocate a number of the items, and this becomes unavailable stock. At the moment, each time the query is run, the information is overwritten.
Please can someone help me if they can think of a better idea of how to do this! Im not that hot on programming, and so a in depth description of what to do would be great!
Emily
View 3 Replies
View Related
Mar 28, 2015
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
View 13 Replies
View Related
Jun 16, 2014
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
View 2 Replies
View Related
Aug 5, 2014
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 Replies
View Related
Nov 2, 2005
I am looking to design a simple stock database for work. This is just an idea at the moment, unless I can get my head round this problem.
I have throught that I can have 3 tables
NEW STOCK(this is where you enter all new and existing stock is)
USED STOCK(this is where all the stock that has been sold used for whatever reason is entered)
ACTUAL STOCK(this is where the items in NEW STOCK have the items sold in USED STOCK deducted from them to give you the ACTUAL STOCK)
PLEASE HELP ME, am I completely on the wrong line or can I do this?
Would be good if I didnt have to use SQL, VBA etc...
View 2 Replies
View Related
Apr 28, 2005
Hi,
I am a beginner with making databases, so am looking for a bit of advice so I can know where to start.
What I'm trying to do is make a stock control database for our drugs dispensary as there are constantly drugs going in and out and they need to be able to keep track of where drugs have gone and what quantity levels they have.
I also have a 2D barcode scanner which scans and reads the drugs barcodes in the following format:
Product Code [Enter]
Product Descripition [Enter]
Batch Number [Enter]
Expiry Date [Enter]
So I would have thought the best thing would be to have a form with these fields in so the user could scan a barcode in filling in these fields. and that would then input the drugs into stock (Obviously there would be a quantity field too)
But where Im getting stuck is how could I then produce a form much like the one above with a search button. So the user could blip in the barcode filling in the relavant fields and then search for that product based on the products batch number?
Any Help would be MUCH appreciated.
Many Thanks
View 4 Replies
View Related
Jun 25, 2006
hey, is there anyone who wil be of help to me by creating me a quick and simple database. It needs to keep track how many spare parts I have. Therefore it needs to show how many of each spare part i have, and be able to update the stock levels with a submit request page that when the submit button is pressed the stock levels automatically update. the information required for each spare part is article (what the product is from), article number ( the unique article number of the product), colour (the colour which its from), spare part (what it actually is) and quantity.
Any more questions please ask
thanks
View 1 Replies
View Related
Dec 14, 2006
I have started making a stock control database, i have spent about a week on it, And i am struggling! There are obviously lots of different ways to actually build them efficiently. My problem is that i want to add stock and take stock out, changing the stock quantity and obviously keeping track of stock. I only want to ammend the stock quantity, in the stock table if the item is not a new item. The stock is added via a log sheet which could also have a new item. My head is beginning to hurt, but with a bit of help from you guys i might be able to crack it.
The Tables I have are:
tblStock
tblCategories
tblSuppliers
tblInventory transaction
tblStock_in
tblStock_Out
tblEmployees
Is this sufficient? Can i add stock and take stock out with one transaction table? Or would i need to add another table? I believe the code would be through a query but again am a little unsure as never done anything like this!
Please help!!
View 8 Replies
View Related
Mar 26, 2007
Hi
I am quite new to access and i can grasp things quite easily.
Basically i am make a stock control data base for a food company. I am making it so theres an order table and a stock table. Basically i want to know how to change the Quantity avalible in the stock table fom the orders table.
I dont know if i have explained that very well. But basically i have related the product name in the stock table to the product name in the order table. So i can add a number so that that the quantity of the product will change on the stock table.
If there are any easier ways of doing this rather than having 2 tables it would be useful
Ive tried all types of things and i cant get it to work. Any help would be very much appriceated.
Thank You
View 2 Replies
View Related
Jan 28, 2008
Hi,
I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.
I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)
I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.
Does anyone know of a sample database that I can download to learn some of this from?
View 2 Replies
View Related
Jun 28, 2005
I need a basic Product database that allows you to have a drop down menu to select a product from a table. In turn this puts the unit price in for you, you then put the quantity in and it calculates the total.
I also have a stock control. When one is bought it takes the quantity down. Very simple I know but I can’t get it to work….
If there are any good basic database templates i could use let me know
I have attached a sample zip file
D
View 2 Replies
View Related
Nov 30, 2007
Dear Reader
I am a realtivly new user and I do not know alot of things about Access
I have set up a database, and within this database, i have a table with my product details (the products are books, details include: ISBN, Title, Stock Level, and Price), I also have a table with customer's information (Name, Contact details, etc). I have created an order form, from which a user selects the customers name from a drop down list, and then he selects his name, and finally the date. Following this the user then works within a subform.
This subform has the following details, Order ID, Book ID, and Quantity Ordered. After this I want there to be a subtotal. I have no idea as to how to set this up, but I am assuming that I would need a query or something, to look up the price of the books, and then to multiply it by the quantity that the customer wants. Following this ideally I would like a small box on the main form saying the total value of the order.
Would their be anyway, to add the book ISBN number into the subform, where the user could either type in the ISBN number or select from a combo box the book title, but either way, both fields are updated, what ever method is used.
From this subform, I would like to be able to print out an order summary/invoice. This would contain all the books ordered by the customer in the current transaction, as well as the customer’s information, and the total of the order.
And my final question is probably the hardest one. Is their anyway (possibly by pressing a button) to automatically minimize the stock value on the products table, by the quantity chosen by the user in the order form? And if the quantity wanted is greater than the amount in stock, for a message to appear on the Order Summary/Invoice stating "On Order"
Using the above method, would it then be possible through the creation of a query to show what customers have ordered in the past, in a tab on the customer's details form.
Thanking you in advanced
CJM
View 11 Replies
View Related
