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

    Unanswered: cant get query right

    i am working on a db for water usage for a small town. the data is address, water meter reading(counter), and date input. i need to compare reading records and get the difference(usage). im getting close but still not right. here is what my query is so far.

    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 DateAdd("m",-1,[tblMo3].[ReadMonth]) = tblMo1.[ReadMonth])));
    im using a short date format for the date. the only thing i need to use per say is the month for this query. but i dont think my DateAdd is working correctly because when i run this query i dont get all the results(records) that i should. any ideas?

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Do all the dates have the same day value for a given address? If not, you'll never get any results (unless the records were both created on the last day of the month).
    Rather than use the whole of the date values, extract the month and year values, and use them:
    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 		((DatePart("m",tblMo2.ReadMonth)=
    			(SELECT Max(DatePart("m",[tblMo3].[ReadMonth]))    
    			FROM tblMonthlyMeterReading AS tblMo3    
    			WHERE DateAdd("m",-1,[tblMo3].[ReadMonth]) = tblMo1.[ReadMonth])))
    AND		((DatePart("y", tblMo2.ReadMonth)=
    			(SELECT Max(DatePart("y",[tblMo3].[ReadMonth]))
    			FROM tblMonthlyMeterReading AS tblMo3    
    			WHERE DateAdd("y",-1,[tblMo3].[ReadMonth]) = tblMo1.[ReadMonth])));
    This was written on the fly, but here's hoping it points you in the right direction!

  3. #3
    Join Date
    Aug 2008
    Posts
    19
    i was tryin to use just the month and year value but i dont think it worked. ill try what you have here. thanks, ill let ya know. and the date value just gets input any day within the month. so it really doesnt matter what day, just the month and year matters. thats kinda the part that i wasnt sure about. ill give this a shot, thanks for the reply.

  4. #4
    Join Date
    Aug 2008
    Posts
    19
    well that didnt do it. i cant get any records with that. i still dont think the month part is right. the only thing that i need is to get the month from the date record and compare it with the previous month. i cant get any date coding to work.

  5. #5
    Join Date
    Aug 2008
    Posts
    19
    ive tried DateAdd, DatePart, and Month. still gets me no records.

  6. #6
    Join Date
    Aug 2008
    Posts
    19

    got it

    heres what was wrong. for my DatePart, you have to use yyyy instead of y. that fixed it. 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]))));

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Thumbs up

    Glad to hear it. Dates are awkward in MS applications - well done!

Posting Permissions

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