Dont Know Wether To Use Count Or Total
Mar 8, 2007
http://i73.photobucket.com/albums/i236/antmoran/databas.jpg
here is a screenshot of 2 of the tables in my database. i would like a query that would count up the amount of times the clientno appears in clientmeeting table. e.g c001 appears twice in the clientmeeting table and i would like to show this in my query. But also i need it to be general so that it will work for the amount of time c002 c003......appear. does anyone understand what i mean and know how to help?
View Replies
ADVERTISEMENT
Oct 28, 2005
One of the tables in the database has these fields
st1: st2: st3: st4: Date:
pass fail fail pass 10/21/05
fail pass fail pass 10/22/05
and so on. What I need to do is extract the number of passes and fails on each of the four stations and put them into a report. User will have to enter either one date or a date range.
Right now, i'm having to create a 8 queries for to find Count of Pass and Fail on each station and make a master query. And I because it's not grouping everything together I can't figure out a way to get a date range...
Please help,
View 10 Replies
View Related
May 16, 2006
i'm on a stump atm
how would u count the total # of client in town to show on the query
View 1 Replies
View Related
Aug 27, 2014
I have a query that is counting perfectly like I want, but I need to sum the overall total of that count. How do I sum a count in SQL?
View 3 Replies
View Related
Mar 6, 2006
I have Datein and Dateout fields with many rows of dates beneath each. I used DatePart in a query to convert each date to a week number; but now need to count, and total, how many times each week number occurs under each field.
I'm sure this can be done easily, but I'm new to expressions, functions, etc, and would greatly appreciate some help!
View 6 Replies
View Related
Jun 29, 2006
Hi everyone,
Managed to build an Access database with switchboard, forms, reports & queries but I'm left with two annoying problems:
1) I have two columns in my main table called "SOURCE" and "SOURCE 2". They both take their data from a table called "SOURCE". I run a weekly query so that the jefes (bosses) can see the fruits of their advertising so I get the the advertising source and the number of times it was used by a client, grouped according to number of times used. My first problem is knowing how to produce a TOTAL at the end of the report of ALL the sources as well as the individual count.
EG: GOOGLE 24
YAHOO 12
MSN 2
TOTAL 38
In design view I have the following:
FIELD: CONTACTID SOURCE WHENREGISTERED
TABLE: CONTACTS CONTACTS CONTACTS
TOTAL: COUNT GROUP BY WHERE
SHOW: Y Y N
CRITERIA: Between [Enter the first date:] And [Enter the last date:]
I haven't used the "SOURCE 2" column due to problem nš2:
2) How do I combine "SOURCE" and "SOURCE 2" columns in my main table in a query? Is it possible? EG on my form a client may have contacted us via GOOGLE the 1st time and then by YAHOO the 2nd time. I want to reflect that in the query, which at the end of the day uses the same table ("SOURCE") to get it's values and then store them in the main "CONTACTS" table.
Hope this isn't too complicated and that I'm explaining myself well. Well done to all those experts whose comments to others have already helped me make some great tweaks, especially with mail merging. Thanks. Chris.
View 4 Replies
View Related
Feb 28, 2006
So...I have a table with 2 fields: Name and Times Accessed
I have three names in the table: Shane, Bill, Chris
There are multiple entries for each name with no primary key set.
The Times accessed are as follows:
Shane 2
Bill 2
Chris 1
Bill 3
Shane 5
This is what I want to do if possible:
I will have multiple text boxes on the form that will display running totals for different people in a table.
So i have three text boxes on the form. 1 for each name. i want that textbox that corresponds to each name to keep the running total for Times accessed. So, for Shane it would be 7, bill would be 5, and chris would be 1.
How/where do I do this? need more info?
Thanks...
Shane
View 1 Replies
View Related
Nov 3, 2004
I have a Report Generated on Access Database, the report is 56 page now i'm tring to put the total records on that report, how do i do that ??? pls help
View 1 Replies
View Related
Jan 7, 2014
In my access database I am using a form and at times a record may need to be updated multiple times. Is there a way to track and count how many times a record has been modified? I already track the last modified date and time.I searched and was not able to locate what I am trying to do, and maybe my search term was not exactly correct.
View 6 Replies
View Related
Dec 18, 2013
I am wanting to display in a text box or on my report the total number or records in my database. Also I have some buttons that filter the results a little, I'm wanting to display the number after I hit the button(s). Would I just add the query to the end of each button I have?
View 6 Replies
View Related
Sep 16, 2013
I am trying to build a report that shows the total count for multiple queries.
For instance I have a field in my table that can be either A, B, or C.
I have 3 queries built, 1 to show me the information for A's, 1 for B's, and 1 for C's.
I would like a report that shows me ONLY the total number of each category. such as:
A= 38
B= 72
C=12
Thus far I have only been able to create one that shows me all of the information from the queries.
View 4 Replies
View Related
Jan 13, 2015
I have the following code:
Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN
[Code] ....
The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...
View 5 Replies
View Related
Mar 5, 2014
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
View 2 Replies
View Related
Oct 7, 2013
In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :
1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10
"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:
1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)
For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?
View 7 Replies
View Related
Nov 14, 2014
I need a solution for the following problem:
For example:
............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1
Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.
A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)
But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.
So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.
Tried 'look up' function but achieved nothing ....
View 3 Replies
View Related
Jul 18, 2013
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.
View 4 Replies
View Related
Sep 20, 2014
I have a report which gives me a count of records found for each group
group 1 - 10
group 2 - 13
group 4 - 82
what i want is a total below this - ive looked at calculated controls however cant seem to get it what soever - I've tried likes of =sum([counts])
View 1 Replies
View Related
May 31, 2014
I have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.
Example:
Field 1: THE
Field 2: 1234
Field 3: BOAT
Field 4: 0001
End Result: THE 1234BOAT0001
Need to add 7 spaces to equal 22 characters.
Fields 1, 2, and 3 can vary in number of characters.
View 6 Replies
View Related
Jan 29, 2014
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..
View 3 Replies
View Related
Aug 8, 2013
I have two fields on the main form and I need a total count of records between the value of the two and then enter the total in a bound field on the main form
View 1 Replies
View Related
Jul 9, 2013
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
View 14 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Mar 7, 2006
heres one that just don't add up
from the beginning,
i've got a form that puts data into a table from a text box named txtcomments, the table allows 255 chars to be stored the comments are show later using a copy of the input form, all this works great. now i'm taking this data and inserting it into another table using sql inset in the vb editor so i can play with it and leave the original in tact everything works fine on my test machine but when i transfere the database to the server i am only permitted to enter 127 chars in the text box i've narrowed it down to the sql statement and know that where the error is occuring ive tried deleting things and reinstalling im just going round in circles and when i transfere the database back to the test machine everything works fine the test machine uses access 2003 but the server uses 2000 this is the only differance
any help would be god like
john
ps sql statement as follows looks fine to me,
DoCmd.RunSQL "INSERT INTO tblHistory VALUES([txtcomments]);"
View 3 Replies
View Related
Apr 28, 2006
Been using simple access for a while now, and this problem is new on me...
Ive got two tables. One has data listed by OLD MATERIAL number (material) and the other has the new Material Number to convert to...
I wanted to simply add a column onto the table to show the new material number (just like a vlookup in excel)...
the odd thing is, i've done this loads of times before and tis worked... Anyone able to help?? :confused: :confused: :confused:
SELECT [2005 DATA].*, MatConversion.[WDB Material], MatConversion.[wdb name], MatConversion.Category, MatConversion.Conversion
FROM [2005 DATA] LEFT JOIN MatConversion ON [2005 DATA].Mat = MatConversion.[Celtic material No];
View 1 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Nov 18, 2005
I have a problem that seems like it could be very similar to this post from a few years ago
http://www.access-programmers.co.uk/forums/showthread.php?t=46645&highlight=vba332.dll
My problem is I dont know how to use Access other then open a program. This is for a customer and they dont know how to do it either as someone else devoloped their database.
Basically it looks like the database is corrupt but I can not open it using a newer version of access because it has some permissions and I dont know how to go about making my access "look" like the other computer.
Any help is appreciated
Thanks
James
View 1 Replies
View Related
