Merging posts with similar values from different rows to one row

[Erik D]

Hi,
I already have a table like this:

MeterName, Period, Value, (Energy if Fjv or Flow if FjvFl)
203-Fjv-Am03, may2003, 255, Energy
203-FjvFl-Am03, may2003, 4050, Flow
205-Fjv-Am05, may2003, 1045, Energy
205-FjvFl-Am05, may2003, 20550, Flow
203-Fjv-Am03, jun2003, 105, Energy
203-FjvFl-Am03, jun2003, 2103, Flow

Before I can solve the rest of the queries I must find the number that represends every Period and Meter and combine on the following way. The new table shall look like this:

MeterName, Period, ValueEnergy, ValueFlow
203-Fjv-Am03, may2003, 255, 4050
205-Fjv-Am05, may2003, 1045, 20550
203-Fjv-Am03, jun2003, 105, 2013


Can any body tell me how I can make it work?

Thanks Erik

 

[WayneRyan]

Erik,

Make query A which groups by month and sums the energy,

Make query B which groups by month and sums the flow,

Make query C which joins the two queries.

Wayne

 

[Jon K]

Or try this query (type/paste in the SQL View of a new query, replacing with the correct table name):-

SELECT FJV.MeterName, FJV.Period, FJV.Value AS ValueEnergy, FL.Value AS ValueFlow
FROM TableName AS FJV INNER JOIN TableName AS FL ON FJV.Period=FL.Period
WHERE Left(FJV.MeterName,7) & "FL" & Mid(FJV.MeterName,8)=FL.MeterName;