Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping (Access2000)

    Morning All,

    OK...I'm having a major brain spasm here...I should know this but am at a loss right now....

    tblDMEMain contains many fields one of which is DateSignedIn (text) and anPVDID (number)
    tblPVD contains anPVDID (autonumber), PVDName (text) and PVDNumber (text)
    qryDMEMthly which is set to Totals and with tblDMEMain and tblPVD with the join on anPVDID and I have PVDName and DateSignedIn set to GroupBy with a couple of Expressions like this: RetCount: Sum(IIf([ret]=-1,1,0))

    Here is my problem and question:

    Problem: When the qry runs it shows each instance of PVDName (I don't want it to)
    Question: How can I Group it so that it will only show the PVDName ONCE?

    Thanks in advance,

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping (Access2000)

    I'm not sure I understand your question. Your query groups by PVDName and DateSignedIn. This means that every unique combination of PVDName and DateSignedIn that occurs in the tables will be returned, for instance

    <table border=1><td>John</td><td align=right>04/21</td><td>John</td><td align=right>04/22</td><td>John</td><td align=right>04/23</td><td>Mary</td><td align=right>04/21</td><td>Mary</td><td align=right>04/24</td><td>Anne</td><td align=right>04/23</td><td>Anne</td><td align=right>04/24</td></table>
    Do you object to the fact that "John" is repeated? There is nothing you can or should do about that in a query. When you use this in a report, you can set HideDuplicates to Yes.
    Or did you mean something else? If so, can you explain what? Thanks in advance.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping (Access2000)

    Since I posted...my brain wisened up and came to it's senses.

    Yes Hans you are correct, I object to "John" appearing more than once...I know why it is appearing more than once...because I have the "DateSignedIn" included in the query... once I remove that...any dups I may have are then grouped accordingly and only appear once...So now I'm on a different path...trying to determine a way to omit the SignInDate from the query and use the report properties to pull in the "SignInDate" (I need this for the purposes of pulling data one month at a time...)

    Any suggestions? And thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping (Access2000)

    If you want to create a report, you don't need to do the grouping and aggregating in a query. You can feed a report the raw data, and do the grouping and aggregating in the report. You might group the data by month, and by name within month, and finally sort by DateSignedIn within the records for a name.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping (Access2000)

    Hans,

    I'm aware of that....but I need to do the grouping in the qry for several reasons: 1)I need to track other fields in the table and then convert them from txt to num and 2) I need to count an ID field ..i.e. for each field that has a yes/no = yes, I need to count it's respective ID ....so I've opted to use the Totals qry. Therefore the grouping is already taking place. In the report calculations are based on the Total Count of the ID field and the Sum of the converted fields.

    ret (yes/no)
    den(yes/no)
    app(yes/no)

    For Instance:

    if either of the above = yes I need to Count it and convert the "-1" to 1 therefore I have "RetCount: Sum(IIf([ret]=-1,1,0))" expression in the qry and in the report I have a txtbox that divides the number of ID's by the Sum of "rets".

    In this case, I think it best to do the grouping and some of the aggregating in the qry prior to the report.

    All is well there...what I need to do is find a method of pulling all the records into the report (and only showing 1 occurrence of each record (PVDName), even though there maybe 2 or more (PVDName)), between one period of time and another period of time (using the DateSignedIn field which I don't want to include in the qry), preferrably using the OnOpen event of the report. At this time I have the following code that I'm working on but havn't completed:

    Dim db As Database
    Dim rs As Recordset
    Dim dteFOM As Date, dteLOM As Date

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDMEMain")
    Set dteFOM = DateSerial(Year(Date), Month(Date), 1)
    Set dteLOM = DateSerial(Year(Date), Month(Date) + 1, 0)

    SELECT tblDMEMain.DateSignedIn
    FROM tblDMEMain
    WHERE (((tblDMEMain.DateSignedIn) Between dteFOM And dteLOM


    If you have any suggestions, I would be forever in your debt
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping (Access2000)

    I don't think you need code for this.

    I would create a subreport based on a query that returns the fields you need, including DateSignedIn, the month and PVDName. You will need to link the subreport to the main report by month and PVDName. so that only the appropriate records will be shown in the subreport.

    Note: as stated, you don't need code. But I would still like to point out that it isn't necessary to calculate the first and last days of a month to limit records to that month. Instead, you could have used
    WHERE Month(tblDMEMain.DateSignedIn) = Month(Date())

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •