How To Create A Totalling Query ?
Aug 23, 2005
Hi. I have a simple access table that contains details of our customers, and what products they have bought from us. Each row represents a product bought, and contains the customer details & the value of that product.
What I wish to do is create a report that lists these purchases, grouped by the client. That much I can do. However I also wish to sort the report by the total spend of the customer.
What I think I need to do is create a query that totals the "income" column for each customer, and then sort the report on that new figure.
All I cant work out, is how ?. Could someone please let me know how best to do this - small words please - my ambition exceeds my ability !.
Thanks all.
View Replies
ADVERTISEMENT
Oct 31, 2006
Greetings,
I am working with a database where I query a table and get a set of data. it is a phone log database and I am trying to find the total number of calls that a certain ext. makes during a certain time. I have it now where it will give me a complete list of the calls made for the dates and the times, but it gives one for each call, I am just looking for a total for each ext not the detailed that it is giving me. any help would be much appreciated thank you
View 5 Replies
View Related
Feb 1, 2006
Hi guys, I am trying to create a query collecting data from 4 tables as well
as calculating totals delivered but it's hurting my head trying to work out
all the joins can anyone help please?
The 4 tables are as follows:
Orders
=====
OrderNumber
CustomerName
CustomersRef
OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
Qty
ItemType
Description
Deliveries
=======
DeliveryNoteID
OrderNumber
Date
DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered
The relationships are pretty straight forward, OrderNumber on Orders and
OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery
Details.
I want the query to list every single item for all orders as well calculating
how the quantity remaining to be delivered based on deliveries so far and Qty ordered.
So I want to create a report that pretty much just displays:
Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.Qty - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered
Can this be done as one query or do I need to join two queries together?
It's hurting my head :(
View 1 Replies
View Related
Apr 4, 2005
Hi,
I have hit a brick wall with my A-Level Access Project. Basically, I have a form for ordering where a user can select items from a combo box linked to a table that contains products. They then select the quantity and a subtotal is calculated for that particular item(s). By that I mean the subtotal generated is only for one selection from the combo box. Any subsequent selections have their own subtotals generated afterwards (I hope I'm not being too vague).
This is the form I am talking about:
http://img.photobucket.com/albums/v318/GOAT2G/FrmMakeOrder1.jpg
The thing I am having trouble with is the final box; "Order Total". I have no idea how to set it to sum all of the above Subtotals to equal an overall total. I will send anyone willing to help a copy of the database via email or something if they need it.
I would be immensely grateful for any help you can give.
Thanks,
Adam
View 5 Replies
View Related
Aug 11, 2006
Hi, I have been looking for some answear, but haven't find the proper one!!
I have a form containing a number of records showing the hours worked in format HH.MM at the end of the form I have the sum of the hours, but the result is not correct.
To make it simple to understand, actualy the total of hous for 11.55 and 11.14 gives me the result of 22.69 but the correct result should be 23.09 (23h and 09m and not 22h and 59m)
How can I get the right total???
Marco
View 2 Replies
View Related
Feb 6, 2008
Good morning everyone,
I am trying to have access total a number of invoices for one project to show the total amount invoiced for that project. I would like to have this number displayed in a form so as I enter dollar amounts, the number goes up automatically.
Sorry if this is a noob question but I am in fact a noob.
Thanks in advance
View 5 Replies
View Related
Dec 13, 2006
Ok, I have a problem. I have two tables with different data pulled from two different areas, but that have the same type of information in them. I need them merged into one query or table so it can be exported into excel.
The problem is that one table has data only once (has a primary key, then things by building and grade). The other table has it's primary key, but then could have more then one building and grade combination (i.e. building 1050 grade 8 membership of 10 more then once becuase off another factor).
I need to merge this stuff by query of some kind into another query or table that totals all things with the same primary key, building and grade. So there is only one line for xxx-xxx building 1050 grade 8 with the grand total of membership for all.
How do I do that?
View 1 Replies
View Related
Sep 2, 2005
I am making a simple tabular form to use as a subform in another.
The form has a cbo box (cboItem_Name)
Quantity (txtQuantity)
Price (txtItem_Cost)
Extended Price (txtExtended_Price)
Selecting an item in the combo puts the price in txtItem_Cost. Default for Quantity is 1. Control Source for txtExtended_Price = [txtQuantity]*[txtItem_Cost] and gives me my extended price.
I have a control in the footer of the form called txtSub_Total. It's Control Source =SUM([txtExtended_Price]).
This Sub Total box generates the #error.
Can anyone tell me why? Do I need to do some other magic with my txtExtended_Price control instead of a simple calculation, like I have?
Thanks for any help you can give a novice.
View 2 Replies
View Related
Oct 5, 2004
i have a query that uses data from a series of tables, the trouble i have is that if one of the tables is null i don't get any data returned.
current query:
Code:Dtime: [Mktbl_otherDTime]![Other]+[Mktbl_UnacDTime]![Total]+[mk_tblBreakdowns]![Breakdowns]
example:
if i have data in:-
[Mktbl_otherDTime]![Other]
[Mktbl_UnacDTime]![Total]
but no value in:-
[mk_tblBreakdowns]![Breakdowns]
i need the total of tables with data so effectivly the third table will be classed as zero
how should i do this please
Andy
View 1 Replies
View Related
Jan 12, 2005
I have a control on my main form which attempts to total the amount from all subrecords on that record. For example, I have fields called ValueA and ValueB in the subform. If I have 5 subrecords in a record, then I want the control to sum all 10 fields (5 for ValueA +5 for ValueB). How can I do this?
View 14 Replies
View Related
Oct 19, 2006
How do I calculate a filtered total from a field in a subform. It needs to be filtered according to [agreementID] and I would like to total the records in another field.
[agreementID] [assessment]
01-pmg18 $20,000
01-pmg18 $15,000
02-pmg18 $13,000
02-pmg18 $21,000
eg: I would like the total assessment for all 01-pmg18's and the total for all 02-pmg18 etc. Can I automate this so it recognizes and totals all assessments according to each unique agreement id group. Customers can have many 'batches" of agreementID's belonging to him. The 18 also refers to his ID number it is typed in. Thanking you in advance!
View 1 Replies
View Related
Sep 20, 2006
Hi there.
I am trying to create a query to show the sum of monies received. However, where there is no money received instead of showing the value as £0.00 it comes up blank.
How do I get it to show it as £0.00 when run?
Regards
Pete
View 2 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Aug 20, 2007
hello!this is my first time in this forum
i hope you can help me with this...
i created a database wich has one table with many fields, what i need is a parameter query to sort the information on that table, the problem is that i want the user to decide wich fields he needs, and if he want to use parameters (criteria) or not on those fields he choose, i wanna now if this is posible with just one query instead of one for each combination of fields the user wants on his query. i hope i make myself clear if i dont just let me know
oh and the criteria is taken from comboboxes in a form, and the query is runned from a comand button on that form too.
thank you
View 4 Replies
View Related
Dec 8, 2007
In database is one Table, where are only two fields (ID and Text):
ID Text
1 AA
2 BB
3 AA
4 CC
5 EE
And in a form I have TexBox where is a text string, for ex. : aa; cc; ee
Im trying to create Query which find all records from Table which contains
"aa" or "cc" or "ee".
If I use concrete values in Query, it works :
SELECT * FROM Table WHERE (((Table.text) In ("aa","cc,ee")));
but when I use refer to a TextBox it doesn't works :(:
SELECT * FROM Table WHERE (((Table.text) In ([TexBox])));
Because it will do [TextBox]="aa;cc;ee" and not "aa";"cc";"ee"
Can anyone help me with it?
Thank you and have nice day.
Jira
View 6 Replies
View Related
Nov 29, 2005
HI,
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
thx guys!:D
View 1 Replies
View Related
Dec 24, 2006
Hi All,
I have 2 tables like below:
TableA:
- Customer
- Customer Industry
Table B:
- Customer
- Project
Each customer has many projects.
My ultimate goal is to create a table like this:
Table C:
- Customer
- Project
- Customer Industry
Trouble is, tables A and B are updated by different persons. A project is usually created first before someone creates the entry in Table A to enter the customer's industry. If someone forgets to update Table A, I will see entries in Table C with industry of customers as blank - which i don't want to.
What is the best way to do this?
I'm thinking of running some sort of queries that will automatically create an entry in Table A when a new customer project has been entered in Table B, so that the person responsible for Table A knows to enter in the industry - but i don't really know how to start, could you help please?
Merry x'mas all!
View 1 Replies
View Related
Jul 25, 2007
:) Hello,
Can anyone please tell me how to create a graphic (a chart like in excel) based on the result of a query? It's just names and values (2 fileds only)
Thankyou.
View 3 Replies
View Related
May 31, 2005
Hi
Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234
I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.
Is there a better more efficient way?
Thanks
coley
View 8 Replies
View Related
Sep 7, 2005
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus
FROM SiteStatus
WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
View 2 Replies
View Related
Sep 11, 2006
Trying to do this sort of query is beyond my comprehension of the software, so hopefully I can get help here.
What I am trying to do is add a total of a certain field together, while not adding the total of another field together. I hope that makes sense.
Anyway, here's an example:
Family: Sum of Duration: Years: Total Birds per Hour:
Tyrannidae 10 1999 0.1
Charadriidae 107.35 1996 26.967
Charadriidae 175.05 1997 45.29
Rallidae 34 1995 46.15
What I am trying to accomplish is that I want to add together the Total Birds per Hour field, without having the duration sum together. What has been happening is that when I sum TBpH, Duration sums, also. Yes, I know I have it as sum of duration, but the point is that has -already- been summed and therefore does not need to be summed again.
Basically, we are trying to see how many birds appear per hour within each year. However, our Duration totals cannot exceed a certain amount per year. Say, we can only get 600 hours in the year of 1997, but instead I end up getting something like 7000 hours because each entry in the family "Charadriidae" gets summed together when it should not.
What was explained to me was that, when we are summing the durations together, only one duration entry at a specific date should be added together. Here is what we believe is happening:
Date: Duration: Species:
199534B 5.5 Ruddy Duck
199534B 5.5 American Avocet
Theoretically, as a total duration for the date "199534B", we should only be getting 5.5, because both of those birds were spotted during a 5.5 hour period, not an 11 hour period if you were to sum those up.
I know that sounds complicated, and I'll try to explain more later. But, are there any suggestions on how I should go about this?
View 6 Replies
View Related
Dec 7, 2006
How do you create a query in code. Actually creating a query that appears under the query section of access. Is this possible?
Thank you
View 2 Replies
View Related
Dec 11, 2006
Hi Guys!
It's me again.
From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:
Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266
Is it possible?
Thanks!
View 3 Replies
View Related
Mar 9, 2007
Hi,
I wrote a script which creates a new table and append records into it. Here are my codes:
For Each rst In dbs.tabledefs
If rst.NAME = "Biweekly_Temp_Table" Then
dbs.tabledefs.Delete rst.NAME
dbs.tabledefs.Refresh
End If
Next rst
qryBtt = "SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='R',[DEDETAIL" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='A',[DEDETAIL" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='E',[DEDETAIL" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, " & _
"First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS Type, " & _
"Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS LeftType, Sum(DEDPARMS" & payp & ".NBR) AS SumOfNBR, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, DEDPARMS" & payp & ".DEDPLAN_CD Into Biweekly_Temp_Table " & _
"FROM DEDPARMS" & payp & " LEFT JOIN DEDETAIL" & payp & " ON (DEDPARMS" & payp & ".EMP_ID = DEDETAIL" & payp & ".EMP_ID) AND (DEDPARMS" & payp & ".DEDTYPE_CD = DEDETAIL" & payp & ".DEDTYPE_CD) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), DEDPARMS" & payp & ".DEDPLAN_CD " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P'))) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"
DoCmd.RunSQL qryBtt
NOW THE ISSUE IS, AN ALERT MESSAGE ALWAYS POP UP SAYING SOMETHING LIKE, "You are about to paste 34590 row(s) into a new table." What would I have to do to get rid of this pop up? Eventually my users are going to use this and I don't want them to be alarmed when the message pop up.
Thank.
Joe
View 4 Replies
View Related
Mar 25, 2007
Hi.
I have three tables. Each link together by relationship. In a form, i will display the relevant data from these three tables by means of a query. However, i realised that i could not perform a delete operation as my database table still exist the particular data even if it is deleted from the query.
How do i create a delete query so that it will also automatically delete all the datas from the 3 table?
Please help.
View 1 Replies
View Related
Apr 10, 2007
I have create these two count querys that count the no of booking in January and the other no of bookings in Febuary
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#12/31/2006# And ([Booking Order].[Start Date])<#2/1/2007#));
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#1/31/2007# And ([Booking Order].[Start Date])<#3/1/2007#));
but i need to create a query which will bring up the two counts in the same query. This is what i have create so far but have got completely stuck and don't know where to go from here.
SELECT Count(BO1.[Booking No]) AS [Count Of Jan], Count(BO2.[Booking No]) AS [Count Of Feb]
FROM [Booking Order] AS BO1, [booking order] AS BO2
HAVING (((BO1.[Start Date])>#12/31/2006# And (BO1.[Start Date])<#2/1/2007#) AND ((BO2.[Start Date])>#1/31/2007# And (BO2.[Start Date])=#3/1/2007#));
View 5 Replies
View Related
