Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View RepliesI've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand.
Here are two examples:
[URL] ....
[URL] .....
I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?
I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).
I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.
I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.
I have two collums, currentowed and currentpaid. i want to add another field to my report showing the percent of currentpaid to currentowed but I can't seem to be able to make it work. I tried:
=sum([currentpaid]/[currentowed]) and it doesn't work.
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
How can I have this query to show no duplicate records.
I would like to only see the first record only of each stock code.
Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.
View 3 Replies View RelatedI have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.
View 3 Replies View RelatedI have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.
View 2 Replies View Relatedhow to have our query show ONLY the records with NO DATA in the company field?
View 12 Replies View RelatedI have a query which is supposed to show the revenue from each advertising source.
PLease see attached in zip file (cant post pics b/c I have less than 10 posts)
As you can see it has revenue amounts for the same source of advertising:
Radio has two amounts, INternet has two etc..
What can I do to only have each form of advertising show up once?
and the revenue amounts summed up for each advertising medium.
I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.
Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.
Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
I'm doing a query and I want it to show records from this week only.
Is there a formula I can use in criteria?
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.
SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table5
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
I would like to create a query to show only the Work Orders that all parts ordered have been received.For the Work Order, each part ordered has a "complete" yes/no checkbox.If I use that field in the criteria of the query, it does return only parts that are "complete." However, it still shows me a Work Order where there are parts ordered but not received "not complete." how to set the query to exclude those cases and only return Work Orders that all parts ordered on the Work Order are received "complete."
View 3 Replies View RelatedI have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...
Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training 1/1/2010 5/1/2010
1 John Smith Python Training 1/30/2011 4/1/2011
1 John Smith Leadership Development 6/27/2013 9/1/2013
1 John Smith Sensitivity Training 9/5/2010 -
2 Hank Joel MS Office Training 8/1/2010 10/1/2010
2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010
2 Hank Joel WHMIS Training 11/15/2010 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012
To turn like this:
Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, -
2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012
I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:
Quote:
SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];
However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:
Quote:
WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")
I placed the following in VBA module:
Code:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
[code]....
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
I m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])
If I select the year from the combo, it works, if I select "ALL" nothing is shown.
here is the full SQL
SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
[Code] .....
I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date.
I have a query that shows only past date records
and a query that shows all records
how do i do this?
I have about 40 queries based on employed trainees from different groups(MMF, MKO, CNP,...). Now, what I'm trying to do is find the percentage of trainees employed from the total trainees, % employed in community from total trainees, % employed at wuskwatim from total trainees,... and so on.
If you take a look at my DB, you will find I have have MANY queries made. Is there an easier way to find this kind of information?
Any suggestions would be appreciated.
18448
I just recently figured out how to count how many jobs a CSR has submitted and how many errors a CSR has made. What I need to do now is combine these two queries and figure the percentage of jobs correct that a CSR has submitted. I tried to combine these to queries in a report and figure it out that way, but that didn't work. When I combined the two queries I lost the individual information in the queries. I tried to combine the two queries but it wouldn't let me. It said that I was trying to combine the fields of a table and a query and that I should use one or the other.
Can anyone help me?
learnasugo
The excel file is how they would like the report to look