I need to execute this query from an ASP . Net page. I'm connected to an Access 2002 DB.
SELECT tblBudget.GroupCode, tblBudget.GCDescriptioon, tblBudget.Amount
FROM tblBudget
GROUP BY tblBudget.GroupCode, tblBudget.GCDescription, tblBudget.Amount
ORDER BY tblBudget.GroupCode;
If I delete tblBudget.GCDescription and tblBudget.Amount from the "GROUP BY" instruction it does not work.
What I want to do is that in case tblBudget.GroupCode is repeated (e.g. several records with the same group code) I want to display only one line summarizing all info for the given group code.
Ok, I made a query that returns a Case Manager, their associated cases, and 3 dates that relate to each of these cases. So its:
Case Manager-----Case Name----Date1-----Date2-----Date3
I only want records to appear that have a value in at least 1 of the 3 date fields. If they have no data in ANY of the date fields I want those records excluded. How do I build this?
I am setting up a query to report on the turnaround time for appication to be processed.
I have a calulated column to calulate the numbe rof days betwen receipt and approval of applications. I now want to be able to run the query and subsequently the report by having a prompt to enter anlower and upper number of days range.
I have used the experssion Between [Lower range] and [ Upper range] to prompt and when I enter say 1 to 5 days the query returns all days.
Any thoughts as to why it is not working and what can I do differnetly?
I'm trying to use IIf in a query criterion, but not having any luck.
I have a field called 'ayr_code' which has values in the format:2015/6
2014/5 2013/4 2012/3 2011/2 2010/1 2009/0 2008/9
I want to return all records where in this field:if the current month is November or December, the first four characters of 'ayr_code' are between five years ago and the current year;
If the current month is between January and October, the first four characters of 'ayr_code' are between six years ago and last year;
So, in October 2015, I'd like to see records with an 'ayr_code' of 2014/5, 2013/4, 2012/3, 2011/2, 2010/1 or 2009/0. In November 2015, I'd like to see records with an 'ayr_code' of 2015/6, 2014/5, 2013/4, 2012/3, 2011/2 or 2010/1.
I've tried to do this using solution one below, but this is not working. I've gone for solution two, but I'd like to know why solution one doesn't work and if there is a way to amend it so that it does.
Solution one [preferable; not working]
Create the following field:
Code : Expr1: Left([cam_sas.ayr_code],4)
Add the following criterion:
Code : IIf(Month(Now())>10,>=Year(Now())-5 And <=Year(Now()),>=Year(Now())-6 And <=Year(Now())-1)
So this should get the first four characters of 'ayr_code' then apply one of two criteria on the results based on whether the current month is after October or not.
This takes the first four characters of 'ayr_code' then if the current month is not after October it adds one to the result, after which it applies the criterion that the final output must be between this year and five years ago.
I am using a simple " >= [BeginDate:] and <= [EndDate:]" in the criterion section of a query to allow a date range to be selected in the query which is used in a report. How can i get that [BeginDate:] and [EndDate:] info available to print on the report so it shows the actual date range being displayed in the report ? thanks.
The user select the class from [Combo28] for the unit already in [Textbox named Unit] from Form 1 and wants to list all the students for that particular class and unit in form 2.
I have tried god knows how many different codes. I've been playing with:
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?
p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???
I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...
I think maybe I should upload the database, let me know.
Here are the names:
Table name: PPM Data
Fields: Company PPM Month PPM Amount
There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.
I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.
Hi, i have a combo box that query a tabel to get the date's i chose from but she repeat the samedate if i have a 2 records with date 12-9-2005 i see 2 in the combo box ho i can join them to see only one date fron them
I have a field on a table that is a memo field. I am trying to write a query to include this field but get the "Can't group on memo or OLE object. Any ideas on why and how can I query with this memo field included?
I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.
Hi, I have just made my maiden voyage on the good ship "Security Wizard" and the water has been a little rough but as yet I still have my sea-legs intact!
Slight problem though... I want users to be able to add data to a table but not to be able to view the whole table if they try and open it in the database window. Now I know reasonable measures would just hide the DB window but I have the threat of Data Protection Auditors looming over me becuase management "forgot" to tell me security was required and it has already been breached before I used the wizard.
Anyway, I did some research on here and I found a method whereby I can set the table so only I have permission to view/add data, then I can get users to add data via a query with permissions set to Owners.
I'm really hoping that someone will come to my rescue and tell me that there's a much easier way than that...
The image above shows results from a query I've created. However, I need to group the results by Product Id and display a sum of the individual Product Id's. So I should have something like:
Commercial Loan Mr Steve F Cobblestone LNCLCLRE £299,491.33 Demand Deposit Mr Steve F Cobblestone DD033 £7,402.81 Demand Deposit Mr Steve F Cobblestone DD043 £3,929.78 Time Deposit Mr Steve F Cobblestone TD013 £12,195.60 Time Deposit Mr Steve F Cobblestone TD018 £961,924.12 Time Deposit Mr Steve F Cobblestone TD025 £113,483.58
Any ideas how I construct a query to display the results as per above??
i want the query to do this 12/02/2001 02:00 18 12/02/2001 05:00 25
i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged
I need to output an HTML table with the following headings:
============================================== Event Title | Event Price | No. Delegates Booked | Total Price ==============================================
How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.
I am using Access 97 and I am getting very stuck when I am making a group by query.
Basically I have a table which has for engineering jobs (and a few other collums): [id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]
I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:
SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1<=10,"Y","N"),IIf([Job Size] Like "S*",IIf(Expr1<=5,"Y","N"),iif([Ref Number] LIKE "ID*",iif(Expr1 <= 1,"Y","N"),"N/A")))) AS Expr2 FROM Sheet1 WHERE (((Sheet1.[Help Desk Type])="CWD") AND ((Sheet1.[DATE ORIGINATED]) Between [Type the beginning date:] And [Type the ending date:])) ORDER BY [DATE ORIGINATED];
I then have made a summary query based on the above query which gives me for every site, between 2 dates; the number of jobs who have met the SLA or not as the case may be.
SELECT [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2, count(*) FROM [MG - CWD SLA] GROUP BY [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2;
What I would like to do is for every like outputted by this query, give the number of jobs for this site between those 2 dates, so then for every line of the query give the percent of jobs which met the sla/not met the sla or not finished.
I have been messing around with distinct and I cannot get this to work! Also been trying to do a join or a subquery, but not sure how to integrate this with working with a query based on a query.
I want to run a crosstab query on a table that looks like the below:
ID STORE RECEIVED ITEM RESPONSE 1, 123, 2006-03-30, Name, Tony 1, 123, 2006-03-30, Position, Baron 1, 123, 2006-03-30, Name, Simon 1, 123, 2006-03-30, Position, Mgr 1, 321, 2006-03-30, Name, Sarah 1, 321, 2006-03-30, Position, Analyst
The crosstab query as it stands only pulls back the first and last records as it wont allow grouping on the value field, which in this case is the response field. All other fields are row headings.
The report looks like this: 1, 123, 2006-03-30, Tony, Baron 1, 321, 2006-03-30, Sarah, Analyst
Any ideas on how I can bring back both reccords, eg Group on the all fields? It should look like this:
Hi everyone. I'm having some issues creating a query i need for work. I'm able to do this on the report level with a group and footer to get this total. however due to further complexity and Queries that will call upon this one, it needs to be in the first level query i create.
What i have is a database. There are Employees, Dates Paid, timesheet dates, Pay TYPE and hours. here is an Example of a few lines of data. this is how the database is created. it creates a new record for every different pay type paid that week.
I have a problem with a SQL query I build. I have a complaints database and for a listbox I need the 5 costumers with the most complaints in the database and the total amount of complaints so far. To do so I tried the following SQL query:
SELECT TOP 5 [Costumername],Count(*) AS [Count] FROM [Complaints] WHERE [Costumername]Is Not Null GROUP BY [Count];
But I get the following error message if I try to run this query: “You tried to execute a query that does not include the specified expression ‘costumername’ as part of an aggregate function.”
I noticed that a pie chart uses almost the same query (only sorted by name, and not the count) and this works, until I try to change that query to the one above. I hope someone can help me with this. Thanks!
I was wondering if there a method in Access to allow me to count the number of items by group?
For example, I have a set of data in a table like so:
SA SA SA SP SP BM
I can choose Group By and the data will group them like SA, SP, BM; and I can choose Count to count the total number of records, but I would like for instance to be able to retrieve the following data:
SA: 3 SP: 2 BM: 1
Currently I'm exporting the list to Excel, and then using Subtotals. It does exactly what I want, but I'm pretty new to Access and wondered if there's a different way! :)
