I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
I have never created a single query that is this complicated.
I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.
The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?
I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.
So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.
So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"
In this case it keeps complaining Callsign.
You did not enter an aggregate function in the TRANSFORM statement.
Here's the statement I created:
SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))
[Code] .....
It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.
ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00
This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :
ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00
IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00
I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL
FROM [Mail List]
WHERE ((([Mail List].[GENDER])="F"))
GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.
Trying to calculate the integer difference between Due_Date and Result_Date excluding weekends and holidays.
I have a table (Holidays) with the dates of the holidays in it. The table looks like:
ID Description Holiday
1 New Years 1/1/2014
2 New Years 1/1/2015
So, if Date_Due: 9/25/2014 and Result_Date: 9/29/2014, then TAT = 2
Since 9/27/2014 and 9/28/2014 are weekends they are excluded from the calculation and only that Thursday and Friday are used in the calculation.
Code:
Private Sub Result_Date_AfterUpdate()
[TAT] = NETWORKDAYS(Due_Date, Result_Date, tblHolidays)
End Sub
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedI would like to have a field in a report show the ratio of two numbers i.e. New Customers is 20 and Lost Customers is 24, and have the ratio field to show 5:6 ratio. I know how to do it in an Excel Spreadsheet (=B1/GCD(B1,B2)&":"&B2/GCD(B1,B2), B1 is new customer and B2 is Lost Customer, but I have not been able to figure out how to make it work in an Access report. I tried to do it in a query, I've tried this code I found on the forum, but could not make is work for my problem.
Public Function CalcGCD(OneNumber As Long, OtherNumber As Long) As Long
Dim lngSmallest As Long
Dim lngGCD As Long
Dim i As Long
lngSmallest = IIf(OneNumber < OtherNumber, OneNumber, OtherNumber)
For i = lngSmallest To 1 Step -1
[Code]...
find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
View 4 Replies View Relatedhi, I have a table that looks like this:
http://i8.photobucket.com/albums/a45/janue/untitled2.jpg
And I wan to query my data that looks like this so that I can display the start time and the end time of each distinct date and in every user:
http://i8.photobucket.com/albums/a45/janue/untitled-1.jpg
How can I do lidat? I appreciate anyone who is able to help me, and this is an access database
please excuse the ignorance, im am i novice in the process on learning this.
i have a project that requires to create a customer service log of all the queries that come in.
i would like to create a form that would enable me to search for the following
query number
date
account name
account reference
then i would like it to list all the return so you can select the entry to open it or either go to that record.
any help is greatly greatly appriciated.
thank you for your time
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
View 1 Replies View RelatedI’m trying to provide the users of my database with a text search facility.
The database deals with documents, and I’d like the user to be able to search by title of document, with partial text matches. E.g. a title might be ‘Health and safety at work act’ - so searching by Health and/or safety gives this document as a result.
The table is called ‘tblDocTitle’ with the field ‘Title’ containing just that.
Ideally the search facility would be some sort of text box in which the text, and then a button to initiate that search.
Hope you can help
Hello,
I have inherited a database that already has heaps of records in it. One of the forms which is used constantly, has a "BookNumber field" (taken from an underlying table called "Participants". At the moment, this number is entered by hand, but it really could be done automatically increasing by 1 each time a new record is created.
My questions are these:
1. If the BookNumber field in the Participants table is a text field, can I use DMax on it, or does it need to be a number field?
2. If it does need to be a number field, will it cause problems to the existing records if I change the text field to a number field in the table design?
3. Where do I put the DMax statement - in the table or in the form? I am assuming that I put it in the form, but point it to the table is that right?
I know you have answered heaps of questions about DMax before (I think I have read just about every one), but I'm still having trouble figuring this out. I tried putting a text box in the form that had an expression that read: =DMax("BookNumber", "Participants")+1 as it's data, but it just came back with an error message.
Please help...
Thanks,
QuietAchiever
hello friends
i am running query as "select CID,name from company " it give me 4 records
i required a sql query in which i can get sr no from 1 - 4
ex.
i get
CID NAME
02 AMM
03 BDJH
05 CHVG
08 JFLJJ
i require
srno CID NAME
01 02 AMM
02 03 BDJH
03 05 CHVG
04 08 JFLJJ
I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:
strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)
The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.
what I am doing wrong?
I've accomplished some simpler things like hiding fields based on the data in another field but nothing very complex. To the point: I have a report that shows data in both rows and columns, the report has a 7 columns, 3 of which could or could not contain data other than zero, in which case the column is not displayed. The problem I have is that it could be any of those three at any given time depending on what data is available for that report and what the user wants to see. Basically, if a given company has no data regarding column "A", then column A is hidden, but the main grouping is done through rows so all companies contain at least 0 for all columns (what I did is that, if a field sums the whole column returns 0, then the column is not visible). What I need to get working is the second part of this, have the columns rearrange themselves depending on which column is hidden (it could be that all 3 are hidden). How could I make this work? (btw, autoshrink is not usefull for this since it only shrinks vertically, not horizontally).
View 9 Replies View RelatedI have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?