FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000.
I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a
recordset. The "Execute" button on the form kicks off VBA which builds and
runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each
ProjectID -- based on a combination of a couple of fields. For instance, the
following is possible:
If the user wants to select ProjectIDs where Employee="Herman" -- without
making a selection on ProjectCategory -- I want only one of the two
"Herman" rows above to be returned. And I don't really care which one.
(Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both
"Herman" rows are returned. I've tried numerous approaches -- this being
the most recent. The SQL statement is being built as I expect, and it's executing.
It's just not giving the results I want/expect. Also, I've hardcoded selections
into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click()
Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _
"first(StartDate), first(EndDate), first(ProjectActive), " & _
"first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _
"first(Employee), first(ProjectCategory) " & _
"FROM qryProjectsForReport WHERE (ProjectActive = "
Select Case optStatus
Case 1
strSQL = strSQL & "True) "
Case 2
strSQL = strSQL & "False) "
Case 3
strSQL = strSQL & "True or ProjectActive = false) "
End Select
If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory =
" & cmbCategory
If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp
If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #"
& dtStartDate & "#"
If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" &
dtEndDate & "#"
If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient
If Len(cmbSector) Then
Select Case cmbSector
Case 1
strSQL = strSQL & " AND Sector1 = True"
Case 2
strSQL = strSQL & " AND Sector2 = True"
Case 3
strSQL = strSQL & " AND Sector3 = True"
End Select
End If
strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;"
MsgBox strSQL
OpenReport strSQL, chkDatasheet
I have 1 table with multi Text columns (location, coaches,ssn etc), and multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only bring back the location for each person that had the 'max' sales (pts).
ex. ssn xxxxxx has 3 sales codes : code 1&2 had less sales aquired then sales code 3. Sales code 1 was location Vt, slsCd 2 was location CA and slscd 3 was location FL. I need to bring back the sum of all three codes but only the location FL as it had the most sales pointing to it. I have deadline of Monday to get this done.PLEASE help
I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as "Furthermore" will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.
The desired output is: Yellow Blue 11/23/2013 11/19/2013 11/19/2103 10/01/2012 10/01/2102 10/08/2010 10/08/2010 12/14/2007
The actual output is: Yellow Blue 11/23/2013 11/19/2013 11/19/2103 11/19/2013 10/01/2102 11/19/2013 10/08/2010 11/19/2013 11/23/2013 10/01/2102 11/19/2103 10/01/2102 10/01/2102 10/01/2102 10/08/2010 10/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.
Here is the SQL:
SELECT Long_List.Yellow,Short_List.Blue FROM ( SELECT DISTINCT BirthDate AS Blue FROM ( SELECT DISTINCT BirthDate FROM citizens UNION
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered] *FormName[The name of the form for editing] *Index[The record ID of the record being edited] *ControlName[The Field being edited] *DateChanged[Date change was done] *TimeChanged[Time change was done] *PriorInfo[for the old value of data being changed] *NewInfo[For the new value of data changed] *CurrentUser[The user base on log in form that was set to Global into another module] *Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database Option Explicit Function Changes() Dim db As Database Dim rs As Recordset Dim strSQL As String Dim strCtl As String Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code: Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
I would like to calculate each item's price only once in the Access report footer. However, when the SUM formula is entered I get the cumulative price for each item (i.e. $70 as opposed to $35).
Is there any way I could just show the price of the first item of the group only? I'm thinking of creating a new field in a query and adding 1s and 0s, but don't know exactly how.
hi. first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful.. now the question (please bear with me if it's too basic, i am relatively new to Access): i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:
In have a table which contains customer address details. I simply want to display a 2-column list. the first column to display a disnct list of city's from the main table. the corresponding column must give the no. of records that contain the entry in the first column.
eg if the main table had 5 customers that lived in london and 6 in Maidstone then
I have a database in Access 2000 that contains multiple entries for each individual - in this case they are 'visits'. I wish to create a query that shows the first and last visit only on any particular day for each individual.
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Hi I'm a teacher and have to set targets for classes, I have created a database which allows me to input targets for the classes. I now need to then display the class target for individual pupils in a new layout. I am sure there is an easy solution and I am looking at the problem from the wrong direction. Thank you for any help
Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!
I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005) I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.
Is there a way I can do this via a query or do I need to put some code together?
I would like to use a query to show only the records where "SRG" is not listed and where there is another group. So in the example data above, I would like the only record to show in the query to be the last record because it's the only one where SRG is not listed along with another group.
The logic behind this is when a problem is initiated and a group assigned, we do some things and document the results. So I am only interested in the records where we have not documented our results too.
Does anyone know how I can do this with a query?
Thanks, Jim
I want the query to only show the data where there is a user group
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
I have a report with field "Plot" as Group Header. I need to print the report which lists the owners of each Plot. When the number of owners is large, it takes more than one page for the listing. How can I force a new page for each new Plot ?
I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays.
I have a subreport and would like to have a header that repeats on each new page if it extends to multiple pages.I tried the suggestion I have seen to create a dummy group header based on an expression of =1 and set the group header "Repeat Section" property to "Yes".header now repeats before every record, instead of just once at the top of each new page.
Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form Field: Option group Value = 1 Value = 2 Value = 3 Field 2: Field 3: Field 4: and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
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?
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
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 have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
Notes (1 to many relationship with People table, based off Number) ----- Number (lookup to People table) Note Date
I need to take all people of a certain type whose newest note is more than 6 months old. Here's what I have so far (it doesn't take into account the "more than 6 months old" part). Notice that I have the GROUP BY because of the MAX() in the outside SELECT statement: SELECT [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, Max([Note Info].[Eff Date]), [App Info].[App Type] FROM [App Info] INNER JOIN [Note Info] ON [App Info].[Soc Sec #] = [Note Info].[Soc Sec] WHERE [App Info].[App Type] Like 'Apprentice*' AND [Note Info].[Eff Date] in (SELECT [Note Info].[Eff Date] FROM [Note Info] WHERE [Note Info].[Eff Date] Is Not Null) GROUP BY [App Info].[First Name], [App Info].MA, [App Info].[Last Name], [App Info].[Soc Sec #], [Note Info].Note, [App Info].[App Type];
Problem is, it's returning multiple notes for a given person.