I now want another query which returns the row which is always the latest STATUSDATE. This is what I have done so far:
SELECT qryFirst.PROBLEM_ID, Last(qryFirst.STATUS_ID) AS LAST_STATUS
FROM qryFirst
GROUP BY qryFirst.PROBLEM_ID;
However, when I want to add qryFirst.STATUSDATE to the SELECT clause, I get an error „You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)“
I need to see what the STATUSDATE is for each of the records returned in the second query. How do I do this? Please!!!!
I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I have the following SQL statement. The COUNT(b.BookingID) AS TB, gives me the number of bookings. I need to ensure that the number of bookings + PrebookCapacity is less than v.Capacity. I do this by:
COUNT(b.BookingID) AS TB + PrebookCapacity < v.Capacity in the WHERE CLAUSE. But when I run this in MS Access 2003, it says Enter Parameter Value for b.TB (It does not recognise b.TB as b.TB is not a table field). If I omit AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)), from the WHERE clause, it works fine, also giving me the TB.
As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.
SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity, COUNT(b.BookingID) AS TB FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)) GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;
I am working on a commercial website that is using ASP and an access database.
I have a pretty elaborate select statement that works, except for part of the ORDER clause.
Here is the scenario.... my client sells automotive parts...
Table 1: (itemsku) holds part numbers Table 2: (itemveh) holds vehicles that work with those parts along with some descriptive clarification (cab size, bed size, years, ...) Table 3: (make) holds the vehicles make name (ford, chevy, ...) Table 4: (model) holds the vehicles model name (F150, Ram, Silverado...)
I need to get a list of parts and their approriate descriptions and sort it by Make, Model, Specific Description, and year.
((itemsku left join itemveh on itemsku.id=itemveh.itemsku_id)
left join model on itemveh.model_id = model.id)
left join make on model.make_id=make.id
where itemsku.item_id=4
order by
make.name,model.name,itemsku.desc_sku
If I put it as listed, all sorts properly, except for the years. As soon as I add to the end of the order by....
, itemsku.yr_beg desc
Then, the desc_sku portion does NOT sort properly, but the years do. I have tried dropping the desc as well to see if that would help, but the desc_sku does not play into the sort factor if the yr_beg is added.
Am I missing something or is their a limit as to the number of fields in an ORDER?
I have a SQL query to gather data from a number of tables (balances, accounts, currencies)
Quote:
SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID]) WHERE BalanceDate = #12/10/2013#
How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)
Along the lines of :
Quote:
WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000
I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
I need to extract the date from CODEDATE field if the "-" character exist in or from DATE1 if the character "-" is not present in the CODEDATE field, then filter the query on the date found.
I wrote that:
Code: SELECT IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) AS DateFound FROM MyTable WHERE (IIf(Mid([CODEDATE],3,1)='-',Mid([CODEDATE],4,10),[DATE1]) Between 1/1/2013 And 31/12/2013)
The query above extra extract some records without sense for me, I tried many other syntax without success.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
My problem now is about evaluating the result of that query in order to calculate quarterly and annual performances. The query returns correctly:
1) NULL - when I don't have values in the given month 2) 0 - when one of the components of the performance formula is 0 3) value (positive or negative) which can include any positive or negative value
In order to calculate the quarterly values I need the performance of the last month in the quarter and the first month in the quarter. My monthly values are stored in columns so say for Quarter 1, I would need something like this:
Code: [mar]/[jan]-1
As you might have thought, the problems come when I have NULL or 0 values. Let me give you a couple of examples.
[jan] is NULL [feb] is a value [mar] is a value
Than my formula would have been
Code: [mar]/[feb]-1
Second case:
[jan] is 0 [feb] is 0 [mar] is 0
In this case I would like the formula return '-' because I want to be able to spot the case in which the quarterly performance is 0 because the initial values were 0 from the case in which the performance was actually 0 (i.e., say [jan] = 101 and [mar] = 101 then performance is 0 which is different from having [jan] and [mar] = 0 thus performance '-').You can combine the three states (NULL, 0, value) with the three months and find many combinations (I have found 27 relevant ones).
I initially thought to use SWITCH in SQL to evaluate the 27 combinations but I found out that SWITCH would evaluate non more than 15 conditions. So I guess the solution should be use a function in VBA which does what I was about to do with SWITCH in SQL.
I have built a test function which evaluates the numerator of my formula using only two cases (CASE 1: all variables have a value <>0, and CASE 2: the first month is null, the second is 0 and the third a value). Here the code:
Code: Function evaluate_s(Var1, Var2, Var3) As Double Select Case Var1 Case Is <> 0
[code]....
My main question here is what is wrong in the syntax of my function. Why is not correctly evaluating at least the two cases in my function? I always get 0 for all records.
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
Code: Public Function getShiftForRecord(DelayStart As Variant) On Error Resume Next Select Case DatePart("h", DelayStart) Case 6 To 16 'Daylight 6:00am - 4:00pm
SELECT (sum(d.cartonsshipped)/m.ctnperskid) AS PalletCount, itemnumber, shipdate FROM orderdata d inner join itemmaster m on d.itemnumber = m.itemnumber
GROUP BY d.itemnumber, d.shipdate HAVING (sum(d.cartonsshipped)/m.ctnperskid) > 0 ORDER BY d.shipdate, d.itemnumber;
and not get the aggregate error (3122). I am guessing it's giving me the error because i try to do a function in the select area.
I have a main form [mainform] with several subforms on it, two of which are on a tab.
On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.
I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.
Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?
I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.
The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.
Any help? Other than writing a MASSIVE If...Then statement...?
Or is there a way to make it so the choice isn't required for the record to show up in the query?
I'd post...but my db is too massive, even zipped. :(
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
I have a DB in .mdb format which works perfectly and when all the code is compiled it results in no problems. However. When I convert this to a .mde file, it doesn't work at all and it suggests that there are compile errors where there weren't any before! Please can anyone suggest what the problem might be as I don't particularly want to run this as a .mdb. Thanks
Debug.Print sSQL Set conn = CurrentProject.Connection conn.Execute sSQL GoTo ThatsIt ErrorHandler: Select Case Err.Number Case -2147217908 'command text not set Case -2147217865 'cannot find table Case 3021 'no records Case Else MsgBox "Problem with cmdArchive_Click()" & vbCrLf _ & "Error " & Err.Number & ": " & Err.Description End Select ThatsIt: conn.Close
End Sub
I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?
I have got the following query but when it actually inserts the date, the dates will be "08/01/2005". Month and day will be swapped if date is less than 12. I don't know it's happening. It's driving me crazy.
It will insert "8/01/2005" for the first date and "21/08/2005" for the second date. my date format is set to australia and i have set the same in my asp page as well. it is giving the same result even if i try direct from the query.
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
Hi all, this is my first post, I'll try and make it a good one :-)
System: Windows 2000 Office 2000
I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists. On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report???? I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run. I don't know why this is happening but I am hoping someone will.
I have a large application in Access 2003 which comprises a main program file and 4 linked data files which between them contain around 50 tables. The prog file sees the data files through linked tables. Everything works well until I do a compact and repair on the data files then unpredictable problems arise. The compaction is only ever done when all users are off the system and one file at a time. Having done a compaction this week I found that one of the tables appeared corrupt when I browsed it via the program file (the table comes from one of the files which contains other tables working OK after the compaction). The data was garbled and what could be recognised was not in the right place - appears to be moved a couple of columns out of place. There were some fields which reported errors also. However when I browse the table in its native file it is perfectly OK. I can solve the problem by re-running the Linked Table Manager in the program file but that is hard work for 50 tables! This has happened previously when I compacted and I would be better understanding the problem than just fixing it! Can anyone help please?
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
The ones with stars are "type" tables. All possible IP's are in the tblIP, models in tblModel, office's in tblOffice etc.
Maintenance types are just like "fuser replacement", "toner replacement", "turned on computer" (IT joke)
anyway, On my main form. If I open the form and click the button I made to go to the previous record or next, I am fine.
If I choose an office, or start a record, I get a message that I have to have a record for IP chosen, that it can't leave this new record until it has an IP because of referential integrity.
If I choose an IP and nothing else, I can leave the new record. If I don't choose IP, but choose everything else, I cannot leave the record. AHHH!!!
The thing is, all my combos on the main form are (as explained above) FK's to lookup tables, and all set up the same: all with referential integrity, 1 - many, cascade updates and deletes.
This 1 table is the only one acting this way.
I am including a print screen of my form, as a visual sometimes helps.
If anyone can help me to fix this, I would really appreciate it. I have never had this problem before.
