Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
My Data gets one of 4 labels: A, R, N, X. I want a pie chart that shows how many of each I have.
I created a query (StatusCounts) using Group By and Count. It lists "OrderStatus" and CountofOrderStatus.
Whenever I use this query to create a chart I get the default Access data not the data that's actually in that query.
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....
TBL-STORE
NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere
TBL-PURCHASES
NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14
I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.
This is one way I've tried to write the SQL, but I keep getting an error...
SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");
I also tried an Count(IiF( and can't get that to work either..
I have a totals query of phone call charges by phone number and I want to show a "budget" amount for each number (same amount for all) and a variance against that budget.
How I can do this at the grouped (phone number) level rather than at the individual call level?
Please see current query below:
SELECT Call1CurrentTbl.電話番号（MSN）, Sum(Call1CurrentTbl.料金) AS 料金OfSum
FROM Call1CurrentTbl
GROUP BY Call1CurrentTbl.電話番号（MSN）, Call1CurrentTbl.[レコード区分], Call1CurrentTbl.表示区分
HAVING (((Call1CurrentTbl.[レコード区分])=2) AND ((Call1CurrentTbl.表示区分)=10));
I have a query that shows member check-ins to a fitness facility. Customer scans their membership card and it logs their customer id, scan date/time (date and time are one field), name, etc. Sometimes the customer will scan their card multiple times if they've left and come back, but I only want to count their first scan. I have a second query that runs off of this one and counts visits by hour for a date range.
So my question is, how do I filter out subsequent scans on each day in a query and how do a I do that when running the query for multiple days? So in the multi-day query I want to count each day they came, but only the first scan of each day? How to do this in the query design grid...
Good Evening All,
I want a cheep calendar that links to queries and can input data as well as display it. It has been on my wish list for 7 or 8 years.
If anyone know how to make one or knows where to buy a cheep one I would really appreciate it.
I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.
My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.
They table layout is all the same. It will be like this:
TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....
TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.
I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.
I need to do a query to find a field value from similar fields. Table 1 has fields(customer id,...) and table 2 has fields (customer id, address,...). I need to use customer id from table 1 to find address in table 2. Both customer id fields in both tables is the same.
View 4 Replies View RelatedI have a database that prints out a contract of line items (as a report) for the home remodeling business.
I use a query to limit the line items which show the description and cost as a subreport.
The only way I could create a total of these line items was to enter an unbound text control, and put in the formula =sum([cost]) where cost being the field name. It works fine, but I need to but the result in a currency format - - two decimals and a $ sign. I sure would like it to do it at the query level, instead of at the report level (will make it more user friendly)
Any suggestions?
Hi all,
I have a registration form where a person can select if they will attend any or all of 3 events. I want to create a report that will say x registered for event 1, y for event 2, and z for event 3.
I have 3 queries which seperate the people by whether they are attending event 1,2,3. In these queries I have CountofID field so that I can sum it.
When i create a query that Sums the CountofID field for each one, it multiplies the values. I played around unsuccessfully with crosstab queries, but to no avail.
Any help would be greatly appreciated.
I have two tables, joined together with a Union query, and what I'd like out of it is a Sum of Categories & Sub Categories, based on all days this year. When run on their own, they give me exactly what I need, but when used with UNION ALL, I have duplicate "Sub Category" Entries...
One from one side of the query, and one from the other..Is there anyway this can be put on just the one line?the SQL I'm using is this...
Quote:
SELECT tblTopLines.Category, tblTopLines.SubCategory, Sum(tblTopLines.Credit) AS SumOfCredit, Sum(tblTopLines.Debit) AS SumOfDebit
FROM tblTopLines
WHERE (((tblTopLines.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblTopLines.Category, tblTopLines.SubCategory
HAVING (((tblTopLines.Category)="Car"))
UNION ALL
SELECT tblSplits.Category, tblSplits.SubCategory, Sum(tblSplits.Credit) AS SumOfCredit, Sum(tblSplits.Debit) AS SumOfDebit
FROM tblSplits
WHERE (((tblSplits.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblSplits.Category, tblSplits.SubCategory
HAVING (((tblSplits.Category)="Car"));
As I said, SQL isn't my thing, but every stone unturned is another feather and all that..
After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?
View 1 Replies View RelatedI'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :
prod1 = 4
prod2 = 3
prod3 = 3
How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)
I have a number of querys which search by month and year, I want to be able to sum up 3 of the querys together and display this in a text box on a form... I have tried dsum etc but I can't seem to do it...
So basically I want the totals which are dsumed into the same form to calculate together in a different query if required... This would be simple except one of the months if from the previous year...
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
I would like to show all linked components (once) in a query.
With inner joins (option 2 when double clicking on a relationship line), it shows all linked components but with duplicates.
When selecting option 1 (only include rows where the joined field from both tables are equal) I only get 1 row due to the fact that not all components have values in all columns.
Therefore, How to create a query that shows all components that have one or more links to other parts. These links have be checked 3 tables.
I attached a overview of the tables involved.
I have a table for timesheet entry for a local building firm. I have a separate table containing employees and rates. I have created query "Qry_ Time Costs" which calculates the cost of hours worked by each employee by multiplying the hours field in the timesheet entry table by the rate field in the employees table.From "Qry_TimeCosts" I have created "Qry_TimeCosts1" in which i have included a running total field for time costs per day using the DSum function.
SELECT Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], Sum(Qry_TimeCosts.Hours) AS Hours, Sum(Qry_TimeCosts.Cost) AS Cost, DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "") AS RunTot
FROM Qry_TimeCosts
GROUP BY Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "")
HAVING (((Qry_TimeCosts.[Build Element]) Is Not Null));
This seems to be working for some projects and not for others. In particular dates 3rd 4th and 5th of June seem to be showing null fields, where all other dates have values. A few projects are showing inaccurate running totals whilst others are working fine.
I have three tables A, B and C.
I want to build a query that displays three separate totals: Total of A, Total of B and Total of C.
When I try to do this, the query has a weird behaviour summing the totals of the totals of the totals, and I don't know why .
I'm attaching a really basic sample of what's happening, where totals SHOULD be $4, $8 and $16 instead of $16, $32 and $64.
Im trying to run the following calculated query:
Total Value on order: Sum([Purchase Price])
I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function
I have a parameter query with a totals row that displays averages. Is there a way to have the average row use only specific records in its calculation based on one of the field's values WHILE still displaying all the records returned by the query.I want only data that has a "YES" value used in the average while still displaying the records marked as "NO"
View 1 Replies View RelatedThe data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).
I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
Code:
Origin Code Weight
Edison 13 4.25
Edison 13c 2.87
Piscataway 10 5.45
Middlesex 23 1.24
Edison 13 5.21
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
Code:
Origin 10 13 13c 23 25 27 27a other
Edison 9.46 2.87
Piscataway 5.45
Middlesex 1.24
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2
Jones; 200
Smith;150
Jones; 300
Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
I have made a database to show me dates that I need to check various documentation from my contractors.
Once a month I want to print out a query/report to tell me what checks need to be made in the following month.
The dates are
Licence Check
Licence Expiry
Van Ins Exp
GIT Expiry
MOT Expiry
Passport Check
Vis Expiry
I need a query table that shows a list of names that have anything to be checked in the month.
So the Column headers would be Name, Surname, Licence Check, Licence Expiry, Van Ins Exp, GIT Expiry, MOT Expiry, Passport Check, Vis Expiry.
Its easy to do a list with one date but when I add multiple dates into the query it looks for names and surnames with the date within the next 30 days for every date and therefor brings back no records.
I have attached the picture. Obviously not all the records will show dates. Some will be blank.
I am reviewing some old database methods and trying to achieve a text box containing the sum of values for items between two dates. At present it is done using a sub-form based on a query of a query.
At first a query collects the values between dates:
Code:
SELECT tblDespatch.Invoiced, tblOrders.Value
FROM tblOrders INNER JOIN tblDespatch ON tblOrders.ID = tblDespatch.JobDespatchID
WHERE (((tblDespatch.Invoiced) Between (DateAdd("m",-3,Date())) And Date()));
and then a secondary query generates the sum of the values:
Code:
SELECT Sum(qrySWBI03.Value) AS SumOfValue
FROM qrySWBI03;
This actually works perfectly but needs two queries and a sub-form for each bit of information and there twelve of them (quotes, orders and invoices for last 3, 6, 9 and 12 months).