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.
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 RelatedI 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 RelatedI 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.
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
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 quick (hopefully) query about adding up consecutive values to get a running total.
Currently I have the following
ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2
etc...
I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.
ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh]
data eg (this is how I would like it to be inputted into the table)
7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
Excel just does it !!!!!
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.
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.
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
I have a query that I need to add trailing spaces to. I have a query that pulls the first and last name of our employees, but after the initial text, I need to add spaces up to 20. So the text of the first name plus spaces would be 20 characters and the last name in a different field would be last name plus spaces would add up to 20.
Thanks for your help.
iwhen i add a field to allow me to add the parameters for a search between to dates. the query will only count the ethnicity of people who complained on the same day and give the others as a single number. i need to allow the user to search between dates as to generate the data for a report.
View 3 Replies View RelatedI have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
like we do when we change our password, if characters are <4 or >10 then warning comes, I am making a password change button and want same thing.
I tried but could not locate any help for this. Can some body help?
Also, if put an If Text2.value = Null then
msgBox "Blank not allowed", vbcritical,"Error"
End If
While debugging I can see Null = Null but it does not give message box.
I tried everywhere in all text boxes of my db and found same thing.
In the fields of the table, among others, there is a date field which represents the starting date of the flat availability. I'd like to create a query which displays the available nights per flat for each month in the current year. For example, Flat1 is available from 11.08.2014, then I'd like to see in the query result 20 nights for August and 30 for September, 31 for October and so on.I created a query which displays only the dates for the current year. Then I joined this query with the Flats table, the join condition is query.date >= flat.valid_from_date and displayed 1 in each row in the result as night. This way, I have the available flats for each day with 1 as available night. Then I tried to summarize the number of nights by month and flat.
View 5 Replies View RelatedI have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 Replies View Related want my query to count only records that are not duplicated based on a set of criteria.I have a table with customer IDs and Call dates and the employee ID. I want a count of the call dates for each customer ID for each employee. However I only want to count as one occurrence if multiple calls happened on the same day. For example
Cust_ID Call_Dt
12315 8/1/2013
12315 8/1/2013
12315 8/1/2013
14253 9/1/2013
Customer ID 12315 would only count a 1 call entry instead of 3 for the date of 8/1/2013
I did a calculation in a query that gives me how many minutes/hours it took to process records. Now what I need to do is show how many records took only 0-2 hours to process. 2-4 hours to process. 4-6 hours to process etc.
View 2 Replies View RelatedWe have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
I am trying to run query on a months worth of dates, have it count based on each day and then display the date and the number?
Table:
field1 - field2 - field 3 - Date
I can run a query one day at a time but would like to run it for the month and get this
12/01/2014 - 15
12/02/2014 - 32
12/03/2014 - 0
12/04/2014 - 12
Hey, I know this is probably a simple thing to do, but I couldn't find it anywhere on the web so hopefully someone here can help.
I have a column of UPC codes which are a bunch of #'s. I need to find the ones that have more than or less than 12 characters.
Thanks
I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.
View 4 Replies View Related