I have an aggregate query that creates a report of "A" by "B". Each "A" can have many "B"s under it. The report works fine. I need to count and display on the report the number of "B"s for each "A". I can't seem to manipulate the "count" property of the query to generate the correct number. Little help??? Thanks....
Mike T
i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
View 3 Replies View RelatedHello,
I have a table as such:
NAME RANK WIN_PERCENT DRAW_PERCENT
The table can have the same people in it multipe times, such as:
NAME LEVEL WIN_PERCENT DRAW_PERCENT
Bob 4 80% 4%
Bob 5 90% 5%
Bob 8 60% 3%
What I would like to do is return only the row in which Bob's Level is the Highest (MAX).
I wrote an aggregate query as such:
SELECT NAME, MAX(LEVEL) AS MAX_LEVEL, WIN_PERCENT, DRAW_PERCENT
FROM MYTable
GROUP BY NAME, WIN_PERCENT, DRAW_PERCENT
The problem is, as you'd expect, this query returns all rows, since I am grouping by WIN_PERCENT and DRAW_PERCENT as well.
Any ideas on what query to use to just return the row with the highest (MAX) LEVEL? i.e.
NAME LEVEL WIN_PERCENT DRAW_PERCENT
Bob 8 60% 3%
Thanks,
Scott
Hey guys, can anybody help
here is what I have:
Job
JobId-----PK
Name
Position
PositionId----PK
Name
Activity
ActivityId-----PK
Name
JobTask as you can see the relationship between Job and Task is M-M
JobId-----PK
TaskId-----PK
PositionTask as you can see the relationship between Position and Task is M-M
PositionId-----PK
TaskId-----PK
ActivityTask as you can see the relationship between Activity and Task is M-M
ActivityId-----PK
TaskId-----PK
Task
TaskId-----PK
TaskName
TaskCompetency as you can see the relationship between Task and Competency is M-M
TaskId-----PK
CompetencyId-----PK
Competency
CompetencyId-----PK
CompetencyName
Keyword
Priority
I have a query like this:
The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority.
But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
Hello! .... I think I posted this in the wrong section of the forum, so here it is again and hope someone's kind enough to help. Basically, I've been asked to show (in a form as well as a report) results on a Monthly, Quarterly and Year-to-Date basis. The table structure I currently have is as follows (additional fields and/or tables can be added if it would help get the results desired):
ID Date Volume/Value
A1 Jun-05 10,000
A2 May-05 15,000
A3 Apr-05 12,000
A4 ------ ------
An May-04 11,000
B1 Jun-05 10,000
B2 May-05 15,000
B3 Apr-05 12,000
B4 ------ ------
Bn May-04 11,000
Part of the trick with this is to be able to show these aggregate values (quarterly, YTD, and on a 13-month rolling basis) as users enter new data for whatever the current month is. Any assistance in helping me get this accomplished would be enourmeously appreciated.
Hi all,
I need some help in sorting out this query.
My Table looks like this..
Manifest Weight StopNo State
------- ------- ------- ------
71545 1000 10 VA
71545 1000 20 TN
71545 500 30 AL
Some Manifests are repeating
There are multiple stops within each manifest (eg.: 10, 20, 30) and each stops has itsown weight info and State.
What I am trying to figure out is the number of manifests, total weight within manifest, and the last stop and state of the manifest
Query:
SELECT ManifestNo, Count(ManifestNo), Sum(Weight), Last(Stop No), Last(State)
FROM RBP
WHERE ShipmentType="ROAD"
GROUP BY ManifestNo;
My Problem: Everything works fine, but the state of the last stop is not displaying properly..
My result is - 71545 3 2500 30 TN
Correct output should be - 71545 3 2500 30 AL
I tried to remove the "last" from State but its gives me the aggregate function error. Any suggession ?
Thanks in advance..
binjos
Hi Everyone,
I am working on a Sales Analysis db, which pulls from:
tSales - Sales Figures (40k records)
tBuildUnit - Bom Build Units (8k records - one per product)
tBom - Bills of Material - All Components (71k records)
tUom - Units of Measure for Products (80k records - 10 per product)
It is quite basic in theory, it looks at a product sold, goes to tBom to get the cost of the raw materials.
I have a query that sums the components by their group, distinguishing between material and labour cost by the component group (cGrp) which is 'WORK' for labour and <>"WORK for materials. I can then sum the cost of the materials for a given product.
Anyway to cut to the issue in hand.
I export from our system (LM4 on btrieve on novell 3.2) into CSVs then manipulate those
And basically i want to have:
Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 350 420 230 190 250
Exodus 0 100 0 0 60 0
Revelation 200 300 140 200
we have 220 customers in this recordset. so i would like just 220 rows.
My data that i export shows every transaction (so i have 40k records)
So i used a aggregate query, grouping by customer account and summing the value, material, and labour fields, however this gives me each customer three times, one per year e.g.:
Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 0 0 230 0 0
Genesis 0 350 0 0 190 0
Genesis 0 0 420 0 0 250
How do i get one what i describe above?
After the query runs i want to export to excel with:
DoCmd.OutputTo acOutputQuery, "qGrouping", acFormatXLS, "d:Output.xls", True
Thanks
Graham
(Carpenter by trade, now in construction acoustics and really shouldn't be doing anything with access!)
I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
Hi guys,
I am completely at a lose here. I have a LARGE sql query that was written before my time and how have the task of customizing it a little. It will run perfectly in Advisor Office (the program it was written for which uses access as the back end db). But if i try and create a query in access directly i get the following error:
You tried to execute a query that does not include the specified expression 'IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type =' as part of an aggregate function
the full IIF statement that is causing it to fail is:
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType
The WHOLE SQL query spans over 4 pages of A4 so i will only put it at the bottom (hoping its not needed to see the error. There are a few unions so i will put the first one in here to show what it is doing.
SELECT IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames) AS Name,
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName AS Manager,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' ') AS PremiumType,
CommnEntries.DueFrom,
CommnEntries.DueTo,
SUM (Matches.Amount) AS TotalMatched,
CommissionCredits.CreditPC AS Credit,
Consultants.FullName AS Consultant,
ROUND ((CommissionCredits.CreditPC * SUM (Matches.Amount) / 100), 2) AS CommissionMatched,
Cheques.Amount AS ChequeAmount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType
FROM ((((((((Clients LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnPremiums.CommnPremRef = CommnEntries.CommnPremRef)
LEFT JOIN Matches ON CommnEntries.CommnEntryRef = Matches.FKeyRef)
LEFT JOIN Cheques ON Matches.ChequeRef = Cheques.ChequeRef)
LEFT JOIN Managers ON Cheques.FKeyRef = Managers.ManagerRef)
LEFT JOIN CommissionCredits ON CommnEntries.CommnEntryRef = CommissionCredits.FKeyRef)
LEFT JOIN Consultants ON CommissionCredits.ConsultantRef = Consultants.ConsultantRef
WHERE Matches.FKeyType = 1
AND Cheques.FKeyType = 1
AND Cheques.ChequeDate BETWEEN #01-Jan-2004# AND #31-Dec-2004#
AND CommissionCredits.FKeyType = 1
AND CommissionCredits.Type = 1
GROUP BY IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames),
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' '),
CommnEntries.DueFrom,
CommnEntries.DueTo,
Cheques.Amount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, ‘Transfer’, ' ')))))), Consultants.FullName, CommissionCredits.CreditPC
If I changed the IIF statement to:
'X' as commisiontype
it all works great but i need this information and its driving me mad. I can not see why it doesnt work within Access.
Any and All help would be great.
thx
Twiggy
I have attached the FULL sql query if you want to look at it all.. it hurts my head to read it all ;)
Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:
tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary
tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want
So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.
Hi ALL,
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error?
Thanks in advance
Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's
From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.
The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd
Any thoughts appreciated, I'm baffled:confused:
I working with Access 2010 and a form with criteria controls, a requery button and a subform that displays the query. I wanted to add a text box that would display some aggregate information about the query results. Like how many results were returned or what the average is in a column of numbers stuff like that.
If at all possible I was hoping to have the text box display aggregate information of the whole query but when a specific entry in the table is clicked I was hoping to have it show aggregate detailed information about that entry opposed to the whole table. So I am not sure if that changes the solution but I wanted to put it out there.In my searches it looks like Dlookup is the way to go but I have seen a lot of people use Dlookup in very different ways so I might be wrong.
I am having trouble with a make-table query. I run a query that populates my website with data daily. I have a bunch of fields in the db that I wanted to be displayed as one field on the website, so in my query I concatenated them. My website is able to distinguish which fields are text and which are memos so I can display multiple lines. Everytime I run the query, it creates the concatenated field as a text field. Is there a way I can default this to a memo type?
Thanks.
I have a form that is to be used to browse through parts using various filters (like category, snippets of part number or description) to find particular components, and then draw information from those components onto the form to be used for other things.
Queries I seem to understand - I've made a query which I've embedded on the form which happily filters the parts using the combo-boxes and text boxes on the form.
What I can't figure out is how to access the information IN the query! There doesn't seem to be a "double-click" event, or a "highlighted row" property, that I can use to tell the query WHICH of the parts returned after filtering is the particular one Im interested in, to draw information from the row.
What I want is a command button that, once the desired part is selected, "sucks" the data out of the query into text boxes on the form.
I've tried something like -
Private Sub cmdSelect_Click()
Me.txtSelectedPartID = Me.qryPartFinder.PartID
End Sub
...but it tells me that the method or data member is not found. When I'm typing the code the auto-complete thingy happily finds "qryPartFinder" when I type "me", but none of the available properties in the list for qryPartFinder seem to be what I need.
How DO you access the data in a highlighted (and/or somehow selected) row?
TIA.
Col.
Access 2003:
I have a query (qryGetRemoteStuff) like this:SELECT * FROM Table1 IN 'k:projectssomedatabase.mdb'I have several databases where i use this technique. I don't want to link these tables to my database.
According to the properties list, "Source Database" is used to store this path to my remote database.
So far so good.
When the path changes i am in a lot of trouble, that is why i want to change the path of this query dynamically. I don't seem able to change this property.
Is this possible? How?
Immediate window:
?currentdb.QueryDefs("qryGetRemoteStuff").Properties("SourceDatabase")Returns "Property not found". Access help F1 doesn't provide me with a workable example.
Hi All,
I am using the following query to retrieve sum of gross total and nettotal :
SELECT BOOKINGNO, AGENT, CLIENT, DATE, ARRIVAL_DATE, NUMOFDAYS, GROSSTOTAL, TOTAL, SUM(GROSSTOTAL) AS SUMGROSS, SUM(TOTAL) AS SUMTOTAL FROM BOOKINGS
MONTH(ARRIVAL_DATE) = PICKMONTH
AND
YEAR(ARRIVAL_DATE) = PICKYEAR
where PICKMONTH and PICKYEAR are the pickup month and year of the travel.
The above query doesn't execute and give error as following
You tried to execute a query that does not include the specified expression 'ACH_REF' as part of an aggregate function.
Please help me
Jigs :confused:
Hi,
I have a tricky question. I have a table like this one:
ID ID2 Value
----------------
1 1 Apple
2 1 Pear
3 2 Apple
4 2 Banana
5 2 Pear
6 3 Apple
And I would like to get a query result like this one:
ID2 AggregateValue
-----------------------
1 Apple-Pear
2 Apple-Banana-Pear
3 Apple
In MySQL I would use the Concat() function with a group by (not tested tough). But in MSAccess?
Thanks in advance,
Massimo
Hi,
Can anyone please help me out with the IIF concept of MS-ACCESS.
I have a table with the following data:
Name Age Place Dept
AAA 13 Mumbai Accounting
BBB 12 Pune Banking
CCC 15 Delhi Finance
DDD 30 Mumbai Accounting
EEE 25 Delhi Finance
Now i need a query output like
Expected output:
---------------
Dept Total-count Mumbai Pune Delhi
Accounting 2 2 0 0
Banking 1 0 1 0
Finance 2 1 0 1
Now i m using the query:
*******************
SELECT [Dept], sum(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi
FROM [Test-Table]
GROUP BY [Dept];
*******************
and the output which i am getting is:
Dept Total-count Mumbai Pune Delhi
Accounting 2 0 0
Banking 0 1 0
Finance 1 0 1
can anyone let me know as to what change should be there in the query so that i get the correct values in Total-count field with the sum of horizontal counts?
Thanks,
SK.
I'm looking to return different average values for each GICS Sector using this query:
Code:
SELECT [GICS Sector], DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null") AS GmMedianByGicsSectorFROM tbl0
WHERE [GICS Sector] = [GICS Sector]
GROUP BY [GICS Sector];
However, I am getting the same value for each:
GICS Sector GmMedianByGicsSector
Consumer Discretionary 0.469556278179105
Consumer Staples 0.469556278179105
Energy 0.469556278179105
Financials 0.469556278179105
Health Care 0.469556278179105
Industrials 0.469556278179105
Information Technology 0.469556278179105
Materials 0.469556278179105
Telecommunication Services 0.469556278179105
Utilities 0.469556278179105
I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()).
I know this doesnt work but this is what I want to do:
SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits
FROM log, Tally
WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9
GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;
Apparenty you cannot have an Aggregate function in a where clause.
Does anyone know a solution, any help would be appreciated
Cheers
Bikeboardsurf
I have a table in MS Access named Invoices containing the following values:
NameInvoice
Jim101
Jim102
Jane103
John104
Jane105
John106
John107
Jim108
Jim109
Jim110
The following query gives me the results you see below:
SELECT Name, Count(Invoice) AS CountOfInvoice
FROM Invoices
GROUP BY Name;
NameCountOfInvoice
Jane2
Jim5
John3
Ultimately, I want to design a SINGLE query that returns ONLY the Max(CountOfInvoice). In this case: 5.
Can anyone help me?
Hi All,
I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.
To calculate the TDAYS i need to SUM the DAYS column for every employee.
When an employee again log in the application he should be aware of the total number of leaves which he has already availed.
Please help me for the same as I am unable to do so.
Kindly revert back for any queries or any further information.
With Warm Regards,
Sanjay
Hi,
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode.
In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date
List - 1 1/1/08
List - 1 1/15/08
List - 2 1/1/08
List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1
List - 1 1/1/08 15
List - 1 1/15/08 8
List - 2 1/1/08 32
List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[MarketCode] = tblListInfo.[MarketCode]
WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
I have a subform (in continuous record view) which displays products on an order items form. Each record has a DMin control to find the lowest price for its product (each product can have many suppliers and therefore many prices). I need to put a control in the subform footer containing the sum of the minimum prices.
Access doesn't seem to want to sum calculated controls, so could someone please point me in the direction of how to go about doing this or achieving the same result through a different method?
Hi,
Is it possible to add a calcualted field to a query that already uses 'sum'?
I want to take the value returned by sum and add the value of another field to it.
Any help will be very much appreciated.
B