I have a field, quantity, which i want to add all the quantities together to show a total. I want to do this using a query as i will use this total for other calculations. When in the query, i have selected "sum" in the totals column in the quantity. What do i need to do extra? I am not getting any results at the moment.
Emily
Hi
I am creating an invoice which has a subform of the products which the customer may purchase.
So within the invoice itself there is:
ProductID | Product_Name | Quantity | Unit_Cost | Line_Total
I am hoping to have the Product_Name as a drop down of all the products which are available and would like the Unit_Cost to automatically lookup the unit cost of the respective Product.
Could someone please help me as i dont know how to do it, at the moment its a case of the individual having to look at the unit cost within the Product_Name drop down, remember it and then enter it lol
Also, how do I have the Line_Total as a calculated field of the Quantity*Unit_Cost? Do I put it within the Order Details table (which handles the many-to-many) or within a query?
Any help or links directing me to answer would be great
Many thanks in advance
:: CNLIFEASITIS
Greetings to you all
I have been struggling with a problem I thought was simple. However I am unable to solve it. I have searched the forum with no luck.
I have three separate tables that have somewhat similar structures. Each have a Quantity in a specific column that I would like to sum across all three tables. The tables contain hourly values for (say) one week (168 values for each contract type). The table structure is as follows:
T1:
Contract_key1
Effective_Date
Quan1
T2:
Contract_key2
Effective_Date
Quan2
T3:
Unit_key
CC_key
Effective_Date
Quan3
The length of T1 and T2 depends on how many contract types are involved. Table T3 depends on the number of units (Number of rows= 168*No. of contracts(units in T3)).
I would like to write a query (will be in VB eventually) that would give me the Sum(Quan1+Quan2+Quan3) for each hour accross all contracts and units. My output table should have only 168 rows (sum all quantities each hour). In the future I may want to sum for each contract, but currently I only need a single sum for each hour.
If this issue has already been dealt with on this forum please point me in the right direction.
Thanks
rmiller
Let's say I have a table with information like this:
Number Quantity Date
1 20 XXX
2 12 XXX
2 65 XXX
1 6 XXX
4 13 XXX
5 32 XXX
3 41 XXX
4 22 XXX
Now, Let's say I want a report as shown below..
Number
1 Total= 26
2 Total= 77
3 Total= 41
4 Total= 35
Well, by now I'm sure you get the point. I want it to add all the quantities with their respective numbers and only show the total on the report. How would I do this?
I used the Northwind database and modified it to suit my needs. This is basically just maintaining inventory, using the "Orders" form and "Orders Subform" to create records in the "Orders" and "Order Details" table which deduct quantities from the "products" table.
Our employees are allowed to use the "Orders" form to issue items from inventory, and this is the method I used for updating quantities in the "Products" table (method 1):
http://support.microsoft.com/kb/252813
Here's the problem: It is entirely possible that a transaction from inventory might be cancelled, and there is no way to replace the inventory levels in the "Products" table, other than doing it manually.
I would prefer a method of maintaining inventory levels in the "Products" table based on current records in the "Order Details" table, whereas, I could merely open the "Order Details" table, delete the canceled transaction (record), and the quantities in the "Products" table would be automatically adjusted.
It doesn't seem like this should be too difficult, I just need a point in the right direction on how it should be done.
Any help will be appreciated.
Thanks,
Mark A.
I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
I need to run a condition that compares 2 field quantities together based on the same item. If they both equal the same then i want it to run a code and change a property for that record.
I will have a OrderQty and a Total receivedQty when these 2 equal each other then it updates the items Status to Received. What is the best way to accomplish this? in vba or queries/sql?
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
View 3 Replies View RelatedI have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working
Sdate is date and it is on main form
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I have developed a query which adds accumulated costs for each client in my DB, I am having trouble adding the VAT tax which is 17.5% to the query though, I seem to get a lesser total once tax is added which can't be correct.
Here is my query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.[CostPerNight], Rooms![CostPerNight]*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_ Facilities!FacilityCost AS TotalCost
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID=Rooms.ClientID) ON Hotels.HotelID=Rooms.HotelID) ON Room_Facilities.FacilityID=Rooms.FacilityID;
I have a table called DSERDBA_CASE_STATUS_CHANGE and it contains the following fields:
DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID,
DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_USERID,
DSERDBA_CASE_STATUS_CHANGE.STAT_ACTIVITY_CODE,
DSERDBA_CASE_STATUS_CHANGE.STAT_COMMAND,
DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE
I’m looking to find CASE_ID's where the BEGIN_DATE is greater than 09/30/2005 and less than 10/01/2006 where the CASE_STATUS went from O to C and where the STAT_REASON_CODE does not equal INT27 and I have come up with this query which works fine.
SELECT
DISTINCT
DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID,
DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_ACTIVITY_CODE,
DSERDBA_CASE_STATUS_CHANGE.STAT_COMMAND,
DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE
FROM DSERDBA_CASE_STATUS_CHANGE
WHERE
(((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)>#9/30/2005# And
(DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)<#10/1/2006#) AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS )="O") AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS)="C") AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE)<>"INT27"));
My problem is that I also want to exclude cases where the case went from CASE_STATUS C to O on the same date that it went from O to C (it closed and reopened on the same day) And I don’t know how to adjust this query to accomplishment this.
Can someone show me how? Thanks
hallo everyone,
i have the following query and i am getting the following output
SELECT Sum(tbl_RFQ_Details_AVOB.[AVOB RFQ]) AS [SumOfAVOB RFQ], tbl_Status.Status
FROM tbl_Status LEFT JOIN tbl_RFQ_Details_AVOB ON tbl_Status.ID = tbl_RFQ_Details_AVOB.Status
GROUP BY tbl_Status.Status, tbl_RFQ_Details_AVOB.Status;
SumOfAVOB RFQStatus
16900110RFQ in Process
12610000Under Review
1200000 Decided
3500000 Terminated
1000000 Terminated Supplier
6000000 Leverage Back
40000000Uncompetitive
500000 RFQ in Preparation
Now i require a new line in addition to this that says that the total value is and the sum of those values must be given.
required output:
SumOfAVOB RFQStatus
16900110RFQ in Process
12610000Under Review
1200000 Decided
3500000 Terminated
1000000 Terminated Supplier
6000000 Leverage Back
40000000Uncompetitive
500000 RFQ in Preparation
totalvalue Total RFQ
How can this be done?
regards,
aravind.s
My company has a microsoft access table with about 1200 IDs containing names, addresses etc.
Now I want to print this out in an excel sheet because I want to create address stickers, but I want every cell in the excell sheet to add information about the columns in the database together. I might be very unclear, let me demonstrate what I mean:
The Acess file might look like this:
id | name | address
1 | john | Street 01
2 | sara | Street 02
Now I want to have 2 excell cells which contain the information like this:
name_address
name_address
In my example, 2 cells with the following information:
john Street 01
sara Street 02
So I dont want 4 cells, I want to add the information together from 1 Id to 1 cell, but I also want to add linebreaks to it, It must eventually become an address sticker
How can this be done?
Hi All,
I am trying to add the infomation from a query and place it in a table so that I can use that data to build a Union Query that plots onto a chart.
Heres what I have:
Dim qDef As DAO.QueryDef
Dim SQL As String
Set qDef = CurrentDb.QueryDefs("QryBaseline")
SELECT tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
FROM tblAudio
GROUP BY tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
HAVING (((tblAudio.Type)="BL")) AND (((tblAudio.PatientID)=[Me]![PaiteintID]));
I have a "tblEvaluate" that I want to store the above selected record.
I am not sure whether to add and Delete, OR Edit and Update?
How do I get the above information into the table - "tblEvaluate"?
I know it's simple, I just have a mental block!
Thanks for you help....ENVIVA
can someone help with this. I have 2 fields in a query
tbl1_nbil = 3
tbl2_nbil = 0
however when I add them using
Bill = [tbl1_nbil] + [tbl2_nbil], I only get a few of the added numbers in the calculated field. In some records it works, but in the majority it does not
It onl seems to add when both fields are greater than zero
tbl1_nbil : tbl2_nbil : Bill
1 : 1 : 2
1 : 0 : 0
2 : 1 : 3
0 : 2 : 0
4 : 0 : 0
I hope I can ask this question so that it makes sense.
I have a totals query that I'm using to make a Chart. The query works fine but I want to change the look abit. What I'm doing is quering a table looking for the total number of times that there is a check box in a field called PDC RTA and totaling the Hrs as well. The data comes back looking looking like this.
PDC RTA-----Count-----Total Hrs
-1 ---------- 6 -------- 53
0 -----------25 -------- 78
My problem is that when I chart this the -1 and 0 show up at the bottom of the bars on the chart. I would like to rename these to fields on the qry qrid then I can chart the new field names so that it will make sense to the user.
Make sense
Jon
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.
A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.
It goes something like
Field Name Data Type
SS# Number (primary key)
Insurance $ Number
PPO YES/NO
HMO YES/NO
Self YES/NO
Spouse YESNO
Children YES/NO
Family YES/NO
Dental Self YES/NO
Dental Spouse.......etc.
What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)
PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.
"Insurance $" is another field name and is where I am trying to get the total to go.
I was thinking of a lookup table but I don't know how to do that in outside of Excel.
I am thankful for any help or even a nudge in the right direction.
~Profector
How can I add comments in an SQL Query mode? ",',?, ; None of these work with text following them.
View 1 Replies View RelatedHi,
I'm trying to add a combo box to my query so when I select the item from the combo box it executes my query. I don't know where to begin... Any help??
Hi,
I have a problem thats really hurting my head and probably has a simple solutions. Please help!
Basicly, I have a query that adds to fields that are numbers, which works fine but when one of the fields is 0 it doesnt add, it just leaves it blank. I have used the nz function in my first 2 fields as some of them are blanks. My calcuation field is simply like:
[returned]+[loaded]
but I get:
Returned Loaded total
0 2
I tried wrapping the nz around my calculation but that gave me like this:
Returned Loaded total
0 2 02
7 8 78
Help! Why doesnt it add 0 and 2 to make 2?
Any help might save my sanity or computer screen.:mad:
Cheers.
Hi all,
I have this select query that pulls out the last 13 distinct values in in descending order from a table. However in order for me to play with these values I need an ID number and ideally i'd like to do this on the fly rather then do a make table and then use that.
Is there any way of adding a 'autonumber' column to a select query on the fly in so that it can be used as reference ?
Thanks in advance,
Mitch...
Im trying to add fields together in a query (these fields are results churned out by an iif expression), however access just concatenates the fields instead of summing. Access will not allow me to change the format of the original fields either.
Any ideas?
My form is based on a select query. There are 2 combos that filter the data displayed in the form by status and property. All of the data is contained in the same table and the query is looking at just the one table. Clicking a button opens a listbox that contains all of the filtered data and clicking a record in the listbox populates the form with that record and closes the listbox. It works good like this.
When I try to add one field (labor) from a second table to the query with that same field added to my form, as long as there is a value in the labor field in the table then it works. If there is no value for labor then the form returns no records. There are records there but they do not have labor assigned yet. I set a default value for the field on my form - (not assigned yet). The listbox displays the records even though there is nothing in the labor field, but clicking a record in the listbox returns an error of 'no current record'.
Actually I dont even have to add the labor field to the form, just having it in the query mucks up the form. I must need an isnotnull statement in the criteria or something.......
Any ideas what I may be doing wrong?
Thanks!
trying to add 2 fields
=[field1] + [field2]
works but I want a space between the 2
how do you do that?
jon
Hi,
Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.
I've done some searches for sequential numbering but couldnt find anything so far.
Thanks, j