Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    19

    Red face Unanswered: query not working right

    is there any other way to select date values other then max because what i have now is only getting results for max date for the month and not all required records show up. heres what i have....
    Code:
    SELECT tblMo1.Address, tblMo1.ReadMonth AS ReadMo1, tblMo1.MeterReading, tblMo1.Complete, tblMo2.Address, tblMo2.ReadMonth AS ReadMo2, tblMo2.MeterReading, tblMo2.Complete, [tblMo2].[MeterReading]-[tblMo1].[MeterReading] AS [Usage]
    FROM tblMonthlyMeterReading AS tblMo1 INNER JOIN tblMonthlyMeterReading AS tblMo2 ON tblMo1.Address = tblMo2.Address
    WHERE (((tblMo2.ReadMonth)=(SELECT  Max([tblMo3].[ReadMonth])   FROM tblMonthlyMeterReading AS tblMo3    WHERE DatePart("m",[tblMo3].[ReadMonth])-1 = DatePart("m",tblMo1.[ReadMonth]) AND DatePart("yyyy",[tblMo3].[ReadMonth]) = DatePart("yyyy",tblMo1.[ReadMonth]) OR DatePart("m",[tblMo3].[ReadMonth])+11 = DatePart("m",tblMo1.[ReadMonth]) AND DatePart("yyyy",[tblMo3].[ReadMonth])-1 = DatePart("yyyy",tblMo1.[ReadMonth]))));
    i tried to test this query out and i input two records (one record on 3 sep 08 and another on 4 sep 08). using select max in my query i only get the result for 4 sep 08 to show up for the month. i switched around and did a select min and got only 3 sep 08 to show up. i tried to do a select all and got a warning saying the maximum number of records the query would return is one.
    what i need to get is one record for each address per month. the month and year part of the query works great. i just dont understand why the query only pulls the result for max and not max per address..... maybe.. heck i dont know..
    anybody know how i can fix this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide some sample data and desired output please?
    Will there ever be more than one record in each month? If so, which record do you wish to obtain and why?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2008
    Posts
    19
    sample data

    address1 readingDate meterReading previousMeterReading meterDiff:Usage
    address2 readingDate meterReading previousMeterReading meterDiff:Usage

    this query gets two records for each address (current month, meter reading and previous month, meter reading) then calculates usage(meter1 - meter2). all that works great.

    what doesnt work is if i use select max it doesnt get all dates within the month for each address. it gives me the max date record for the month. so addresses with dates that are less then max dont show up. this is for water usage for a small town. the table only has fields for address, reading date, meter reading, and completed fields.

    hope this helps...

  4. #4
    Join Date
    Aug 2008
    Posts
    19

    Red face

    just tested changing the date in the table. when i change the dates in the table to be the same (3 sep 08 for both addresses), both records show up in my query. thats what i want. but if the dates are not the same it should still show up on my query. it is supposed to bring results for all dates within the month.

    in design view i have this in the criteria....
    Code:
    (SELECT  Max([tblMo3].[ReadMonth])   FROM tblMonthlyMeterReading AS tblMo3    WHERE DatePart("m",[tblMo3].[ReadMonth])-1 = DatePart("m",tblMo1.[ReadMonth]) AND DatePart("yyyy",[tblMo3].[ReadMonth]) = DatePart("yyyy",tblMo1.[ReadMonth]) OR DatePart("m",[tblMo3].[ReadMonth])+11 = DatePart("m",tblMo1.[ReadMonth]) AND DatePart("yyyy",[tblMo3].[ReadMonth])-1 = DatePart("yyyy",tblMo1.[ReadMonth]))
    this is the part that is giving me problems, but not all of it. my DatePart code for month and year is working correctly. maybe this helps....

Posting Permissions

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