group to combine parameters

A

anil

I have following tables and query with example data

tblGroup - GroupID ,GroupName
1 , GTP1
2 , GTP2

tblParameter -ParameterID , ParameterName
1, BOD
2, pH
3, SS
4, TP

tblGroup_Par - Group_ParID, GroupId, ParameterID
1, GTP1, BOD
2, GTP1, SS
3, GTP1, pH
4, GTP2, BOD
5, GTP2, SS
6, GTP2, TP

qryWW - SampleID, SiteName,ParameterID.
101, TT1, BOD, SS, pH (in crosstab as example)
102, TT2, BOD, SS, TP

Now combining all these tables and query,I want the Final results as
(from the following query)
101, TT1, GTP1
102, TT2, GTP2

using the query
SELECT DISTINCT [TempqryWW].Location, [TempqryWW].SiteCode,
[TempqryWW].WWMonth, [TempqryWW].WWYear, [TempqryWW].SampleID,
[TempqryWW ].ParameterName, tblGroup.GroupName
FROM (tblGroup_Parameter INNER JOIN tblGroup ON
tblGroup_Parameter.GroupID = tblGroup.GroupID) INNER JOIN [TempqryWW]
ON tblGroup_Parameter.ParameterID = [TempqryWW].ParameterId

but,it gives me result as
101,TT1,GTP1
101,TT1,GTP2
102,TT1,GTP1
102,TT1,GTP2

Can someone please help me in getting the result proper.
Thanks
Anil
 
R

Rob Parker

Hi Anil,

I notice this is a re-post. I suspect that you got no replies previously
because your problem, as posted, can't really be solved because there's some
vital information missing.

Where do SampleID and SiteName come from, in qryWW?

What is the SQL for qryWW?

What is the SQL for TempqryWW?

It also seems likely that you are using lookup fields in your tables, which
will only serve to confuse things. tblGroup_Par, for example, seems as
though it should contain:
1, 1, 1
2, 1, 3
3, 1, 2
4, 2, 1
5, 2, 3
6, 2, 4

Also, it's highly unusual to combine a crosstab query with other queries
(except for lookup tables); crosstabs are generally used to display
"long/narrow" normalised data as "short/wide" user-understandable data.

Post full details and maybe someone (perhaps me, perhaps someone else) will
be able to help.

Rob
 
A

anil

Rob, I was just trying to combine the first and second email to
provide better idea. Sorry I confused you.
I will try to explain again.

Basically, I generate sampling schedules for each month for sites,
that is different parameters for different towns.Every thing was fine,
till laboratory told me to combine parameters into groups.So I am just
trying to combine the parameters into groups.

I have following tables and query with example data

tblGroup - GroupID ,GroupName
1 , GTP1
2 , GTP2


tblParameter -ParameterID , ParameterName
1, BOD
2, pH
3, SS
4, TP


tblGroup_Par - Group_ParID, GroupId, ParameterID
1, 1, 1
2, 1, 3
3, 1, 2
4, 2, 1
5, 2, 3
6, 2, 4

qryWW - SampleID, SiteName,ParameterName. (Few other fields like
ParameterID,SampleTime.... had been ommitted)
(Got this query results by combining tables -
tblsample(SampleID,Month,Year,...)tblSite(SiteID,Sitecode,...) and
tblParameter)This is generated as sampling schedule for month.Provided
just results and few other fields like Month and year had been
removed, to avoid complexity
101, TT1, BOD
101, TT1, SS
101, TT1, pH
102, TT2, BOD
102, TT2, SS
102, TT2, TP


Now combining all these tables and query,I want the Final results
as
(from the following query)
101, 8, 2008, TT1, GTP1
102, 8, 2008, TT2, GTP2


using the query
SELECT DISTINCT [qryWW].SampleID, [qryWW].Month, [qryWW].Year,
[qryWW].SiteCode,tblGroup.GroupName
FROM (tblGroup_Parameter INNER JOIN tblGroup ON
tblGroup_Parameter.GroupID = tblGroup.GroupID) INNER JOIN [qryWW]
ON tblGroup_Parameter.ParameterID = [qryWW].ParameterId


it gives me result as
101,TT1,GTP1
101,TT1,GTP2
102,TT1,GTP1
102,TT1,GTP2

I hope if I had made clear this time.

Thanks
Anil
 
R

Rob Parker

There is still no detail on exactly what the fields are that you are wanting
in your final query (eg. are '101' and 'TT1' SiteCode, SiteID, or SampleID
fields), and how they are related in the underlying tables.

It's likely (but by no means certain) that your problem will be solved by
joining on more than one field from your tables or queries; but without full
details of all of them, I can't help you.

Rob
 
A

anil

Dear Rob
I will try to explain in different way.
tblWWSam_Sch is table where sampling schedule is generated for
WasteWater every month while tbllog is table where schedule is saved
for both Water and Wastewater.I had joined both tables to get data for
WasteWater only.(Consider tblWWSam_Sch and tblLog as seperate new
tables considering as new sub project)

SELECT tblWWSam_Sch.Location, tblWWSam_Sch.SiteCode,
tblLog.SiteAddress, tblLog.SampleID, Month([ADate]) AS WWMonth,
Year([ADate]) AS WWYear, tblWWSam_Sch.ParameterId,
tblWWSam_Sch.SampleTaken, tblWWSam_Sch.ParameterName
FROM tblLog INNER JOIN tblWWSam_Sch ON (tblLog.SiteID =
tblWWSam_Sch.SiteID) AND (tblLog.ParameterName =
tblWWSam_Sch.ParameterName)
WHERE (((Month([ADate]))=Month([Forms]![Frmww_sampling_Sch_Reports]!
[WWMonth])) AND ((Year([ADate]))=Year([Forms]!
[Frmww_sampling_Sch_Reports]![WWYear])));

This query is saved as TempqryWW_Groups. Then this query is joined
with tblGroup and tblGroup_Parameter as follows

PARAMETERS [Forms]![Frmww_sampling_Sch_Reports]![WWMonth] DateTime,
[Forms]![Frmww_sampling_Sch_Reports]![WWYear] DateTime;
SELECT DISTINCT TempqryWW_GROUPS.Location, TempqryWW_GROUPS.SiteCode,
TempqryWW_GROUPS.SiteAddress, TempqryWW_GROUPS.WWMonth,
TempqryWW_GROUPS.WWYear, TempqryWW_GROUPS.SampleID,
TempqryWW_GROUPS.ParameterName, tblGroup.GroupName
FROM (tblGroup_Parameter INNER JOIN tblGroup ON
tblGroup_Parameter.Groupid = tblGroup.GroupID) INNER JOIN
TempqryWW_GROUPS ON tblGroup_Parameter.ParameterID =
TempqryWW_GROUPS.ParameterID

tblGroup - GroupID ,GroupName
1 , GTP1
2 , GTP2


tblParameter -ParameterID , ParameterName
1, BOD
2, pH
3, SS
4, TP


tblGroup_Parameter - Group_ParameterID, GroupId, ParameterID
1, 1, 1
2, 1, 3
3, 1, 2
4, 2, 1
5, 2, 3
6, 2, 4


At present I have 2 options.

Option 1 - I have already generated sampling schedule and now convert
the parameters details into their respective groups.(Preferred option
since all modules/functions/queries had been designed accordingly and
I would have to make change for laboratory schedule in the last query/
report)
Option 2 - I have got the table Groups and table Parameter links set
up and now I regenerate the sampling schedule starting from scrach
writing all modules and queries considering for groups rather than
parameters.

I hope it helps.
Anil
 
R

Rob Parker

Sorry Anil,

This has become much too complicated for me. Each clarification I ask for
only leads to more complication; I don't have the time to spend on trying to
work out what you've got and what you want, particularly when you don't
provide specific details which I request; instead, you say things like
"Consider tblWWSam_Sch and tblLog as seperate new tables considering as new
sub project" - which I cannot understand at all.

Rob

Dear Rob
I will try to explain in different way.
tblWWSam_Sch is table where sampling schedule is generated for
WasteWater every month while tbllog is table where schedule is saved
for both Water and Wastewater.I had joined both tables to get data for
WasteWater only.(Consider tblWWSam_Sch and tblLog as seperate new
tables considering as new sub project)

SELECT tblWWSam_Sch.Location, tblWWSam_Sch.SiteCode,
tblLog.SiteAddress, tblLog.SampleID, Month([ADate]) AS WWMonth,
Year([ADate]) AS WWYear, tblWWSam_Sch.ParameterId,
tblWWSam_Sch.SampleTaken, tblWWSam_Sch.ParameterName
FROM tblLog INNER JOIN tblWWSam_Sch ON (tblLog.SiteID =
tblWWSam_Sch.SiteID) AND (tblLog.ParameterName =
tblWWSam_Sch.ParameterName)
WHERE (((Month([ADate]))=Month([Forms]![Frmww_sampling_Sch_Reports]!
[WWMonth])) AND ((Year([ADate]))=Year([Forms]!
[Frmww_sampling_Sch_Reports]![WWYear])));

This query is saved as TempqryWW_Groups. Then this query is joined
with tblGroup and tblGroup_Parameter as follows

PARAMETERS [Forms]![Frmww_sampling_Sch_Reports]![WWMonth] DateTime,
[Forms]![Frmww_sampling_Sch_Reports]![WWYear] DateTime;
SELECT DISTINCT TempqryWW_GROUPS.Location, TempqryWW_GROUPS.SiteCode,
TempqryWW_GROUPS.SiteAddress, TempqryWW_GROUPS.WWMonth,
TempqryWW_GROUPS.WWYear, TempqryWW_GROUPS.SampleID,
TempqryWW_GROUPS.ParameterName, tblGroup.GroupName
FROM (tblGroup_Parameter INNER JOIN tblGroup ON
tblGroup_Parameter.Groupid = tblGroup.GroupID) INNER JOIN
TempqryWW_GROUPS ON tblGroup_Parameter.ParameterID =
TempqryWW_GROUPS.ParameterID

tblGroup - GroupID ,GroupName
1 , GTP1
2 , GTP2


tblParameter -ParameterID , ParameterName
1, BOD
2, pH
3, SS
4, TP


tblGroup_Parameter - Group_ParameterID, GroupId, ParameterID
1, 1, 1
2, 1, 3
3, 1, 2
4, 2, 1
5, 2, 3
6, 2, 4


At present I have 2 options.

Option 1 - I have already generated sampling schedule and now convert
the parameters details into their respective groups.(Preferred option
since all modules/functions/queries had been designed accordingly and
I would have to make change for laboratory schedule in the last query/
report)
Option 2 - I have got the table Groups and table Parameter links set
up and now I regenerate the sampling schedule starting from scrach
writing all modules and queries considering for groups rather than
parameters.

I hope it helps.
Anil
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

group to combine parameters 2

Top