I 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 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 need to show 10 records on one form (10 customers info), and for now I have 10 subforms to do that.
Is this the way to do it?
I work at a small mental health clinic and am trying to accomplish the following.
What I have:I have one table that lists all of the patients, their phone number, address, etc.
In another table, it lists the medications that each patient takes, as well as the strength, dosage, etc.
The two tables have a relationship via the "Patient ID" column in each table.
Most patients have multiple entries in the second table, as most patients take multiple medications.
What I am attempting to accomplish:The main form in my database simply lists all the patients names in datasheet view. I want to be able to click a Patient's name, then go to a form that lists all of that patients medications. I've attached a screenshot that encapsulates precisely what I am looking to accomplish.
Since most patients take multiple medications, I need to be able to list multiple records in a single form, grouped by medication name, just like in the attached image.
I am uncertain as to how to accomplish this. I've tried using subforms but it doesn't list the data at all? Also, it looks like an Access report can create precisely what I need, but I need to have a live form that I can use to update data real-time.
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 3 Replies View RelatedHow 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 need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies View RelatedI 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 three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
See the attached file ....
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] .....
Hello again
Sorry, just posted this in reports, moved it now.
Could someone point me in the right direction please. I have seen a thread similar to this before but I can't find it now.
I have a query that is made up from 3 different queries.
These 3 queries show the stats totals in the last x weeks.
qry2Week (colleague stats from last 2 Weeks)
qry6Week (colleague stats from last 6 Weeks)
qry13Week (colleague stats from last 13 Weeks)
This query shows every day and also the above 3 queries are linked in.
qryAll (every day and 2, 6, 13week stats totals)
The problem is if there are no stats in the last 2 weeks no stats will show up at all.
If I take out the (is not null) from the individual queries I get duplicate lines in my main query (qryAll)
How do I show all the information without having duplicate lines.
Cheers
Kev.
I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCal Equipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());
how to get the query for the report to return the records.
I have a database that contains different departments per office location with the dept. codes such as 100, 101, 102, 103 etc.
I like to have a criteria in a query that will give me all departments that are running from one office location OR if I do not want one dept. to show in my query to be excluded.
Also, the way I currently set the parameters is, it is asking for the office location by state, county, city, address and department code. I set the department criteria as Not [Department] which excludes the dept. that I do not want to see in the query, BUT I also want to have an option that when I run the query to SEE all the departments.
Is it possible to have a criteria like that?
when running the attached report I am getting duplicate records. If using the protocol specified tc02026 there should be 8 records total. When running the report it pulls from both queries which have select criteria. How do I limit the report to only give me the 8 records and eliminate the duplicates? When I run each query it only gives me the 8 records I am looking for. Each Sample# in the report is a unique number(to help see the replicates).
Thanks