Group By Is Not Returning One Record Per ID

Jun 10, 2005

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:

ProjectID---StartDate---Employee---ProjectCategory---... <other fields>
1-----------01/01/05---Herman-----App Dvlpmt
1-----------01/01/05---Hortence---Consulting
1-----------01/01/05---Herman-----Consulting
1-----------01/01/05---Hortence----App Dvlpmt

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

End Sub

Thanks in advance!
DM1968

View Replies


ADVERTISEMENT

Qry To Group & Sum All Data But Only Returning The Location Of Where Max Pts Was

Jan 21, 2007

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

View 2 Replies View Related

Forms :: Combo Box Keeps Returning First Record In Query?

Jan 27, 2014

I am using A2007 and this one combo box is returning the first record of a query no matter what record I pick. My other combo boxes are working fine.

When I pick a record the "after event" works fine by placing the data in the proper text boxes and then returns the first record in the query.

View 3 Replies View Related

Forms :: DLookup Returning Value Of First Record Rather Than Specified Criteria

Oct 28, 2013

I'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify.

View 6 Replies View Related

Queries :: Correlated Query Returning Only 1 Record And Repeating

Aug 10, 2014

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

[Code] ....

View 11 Replies View Related

Modules & VBA :: Audit Trail Returning Primary Index Of A Record Changed

Apr 24, 2015

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?

View 5 Replies View Related

Sum Only First Record Of Group

Jul 17, 2012

My data is structuredin the following format:

ITEM......................PRICE
X..............................$10
X..............................$10
Y------------------------$25
Y------------------------$25

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.

View 3 Replies View Related

Marking One Record In A Group

Apr 30, 2008

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:

Prop Date Category ID ID_unique
x1 2/3/08 001 2351 1
x1 2/5/08 001 2351 0
x1 2/6/08 001 2351 0
x1 2/9/08 001 2351 0
x1 2/3/08 001 2352 1
x1 2/4/08 001 2352 0
x1 2/3/08 001 2354 1
x1 2/4/08 001 2354 0
x1 2/8/08 001 2354 0

so for each group of records with the same ID, i need to mark only one of them.
is it possible?
thanks.. l

View 12 Replies View Related

Record Count On Group

Oct 5, 2004

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

London 5
Maidstone 6

etc

View 1 Replies View Related

Showing First And Last Record In A Group

Aug 30, 2005

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.

Can anyone help me?

View 7 Replies View Related

Sum Function Only Adding First Record Of Each Group

Mar 8, 2008

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.

Any help would really save my day. Cheers!

View 11 Replies View Related

A Record For A Group Being Displayed For An Individual

Oct 18, 2005

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

graham

View 3 Replies View Related

Using 'Group By' To Find A Specific Record...

Oct 10, 2005

Hi,
I have a table with various customer items, a current price and the date the price changed.

EXAMPLE DATA

CustID/ItemCode/DateChanged/Value
AAA 21 01/01/2004 £3.00
AAA 21 01/06/2005 £5.00
AAA 21 01/03/2005 £4.00
AAA 22 01/01/2005 £6.00
AAA 22 01/06/2005 £7.00
BBB 25 01/01/2005 £8.00
BBB 25 03/07/2005 £9.00
BBB 26 01/06/2005 £1.00
BBB 26 01/09/2005 £2.00

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?

Cheers
Red [CODE]

View 2 Replies View Related

Show Record If Our Group Is Not Listed

Oct 19, 2006

I have a query based on a table that has the fields as shown below. When I run the query, I will get a separate record for each group as shown below.

prb_no | seq_no |commitment_user_group_cd
602 |3 |ERC
602 |3 |MCE
602 |3 |SRG
602 |4 |MCE
602 |4 |SRG
602 |5 |SRG
602 |6 |MCE

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

View 6 Replies View Related

Updating Record Using Check Box Group

Jan 19, 2007

Hi, ive got a form which requires a user to select atleast 1 option for a check box group.

when i pass the results of the form to access all of the checkboxes in the group are update although not all were selected by the user.

what can be causing this problem. The values are being passed to the correct fields in the DB but it still updates across the board.

Thanks

View 1 Replies View Related

Detailed Record Numbers By Group In QUERIES

Oct 24, 2007

I have a table that looks like this:

caseID
date
value

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.

Any ideas??

View 3 Replies View Related

How To Print Each Record Of A Group On A Separate Page

Oct 23, 2004

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 ?

View 2 Replies View Related

Reports :: Subform Only Showing Last Group Record

Feb 25, 2014

I have one query that the main form is based on and another query the subform is based on.

I linked the subform to the main form by a common field "Branch"

My main form displays Grouped Employee overhead totals based on Branch

Example:
Branch 1 Employee overhead cost...........15,000.00
Branch 2 Employee overhead cost...........25,000.00

The subform displays grouped branch expense overhead totals based on Branch

Example:
Branch 1 Branch expense...........125,000.00
Branch 2 Branch expense...........155,000.00

I am trying to display both the employee overhead total and branch expense in the Branch Header of the mainform.

Example:
Branch 1 Employee Overhead...15,000.00 Branch Expense...125,000.00
Branch 2 Employee Overhead...25,000.00 Branch Expense...155,000.00

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.

Example:
Branch 1 Employee Overhead..15,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00

Then, I tried just putting a text box in the main branch header subreportName.Report.TotalField

When I run the report it returns all employee overhead with the Branch 2 total for every branch in the mainform...

Example:
Branch 1 Employee Overhead..15,000.00 Branch Expense...155,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00

View 4 Replies View Related

Reports :: Sub-report Group Heading Repeats After Every Record

Apr 2, 2015

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.

View 1 Replies View Related

Forms :: Repeat Record Entry Based On Option Group Selection

Sep 10, 2014

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.

View 3 Replies View Related

Reports :: Sum Group Items By Specific Item And Hide Details Of Group Summing

May 29, 2015

Despite Google I can't seem to figure this out.

I have some data in a format similar to:

Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000

I want my report to total by style, without details and to look like:

driver careful 13500
racer reckless 12500
snail slow 6000

How do you get a report to sum the group items by a specific item and to hide the details of that group summing?

View 2 Replies View Related

Reports :: Control Group Expression For Group In Report?

Mar 28, 2013

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?

View 5 Replies View Related

Returning All Dates Between Two Dates In The Same Record?

Nov 3, 2005

Hi,

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.

Example:

StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004

Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004


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.

Is this possible?

Any help v.much appreciated.

TS

View 3 Replies View Related

Forms :: Option Group - Warn User When Neither Of 2 In Option Group Not Ticked?

Jul 25, 2013

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>.

View 1 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

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?

View 14 Replies View Related

Returning More Than Max

Mar 28, 2006

People
-----
Name
Number
Type

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved