Repost- creating comparative report

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I apologise for reposting so soon but I wondered whether as I'd sent a
supplemntal posting and that put a + against the fisrt post then it may have
been overlooked it was on a Sunday as well!!!.
So here is my problem
I have a report based on a query. I have a field called txtmonthlabel and a
number of fields that hold numeric data eg txtdata1, txtdata2,txtdata3 etc
etc. The query finds my data but displays it on the report as:
txtmonthlabel 1
txtdata1, txtdata2,txtdat3 etc etc
txtmonthlabel 2
txtdata1, txtdata2,txtdat3 etc etc

What I want is:
txtmonthlabel 1 txtmonthlabel 2
txtdata1, txtdata1,
txtdata2, txtdata2,
txtdata3 txtdata3
etc etc
etc etc


So the data might look like this:

March 2003 March 2004
Txtdata1 label 2345 5678
Txtdata2 label 46575 45758
Txtdata3 label 454747 64474
etc
etc

I have tried using the column function but that doesn't seem to work. Can
someone help?
TIA
Tony Williams
 
It would help if you showed some actual data with field names. It looks like
you might need to normalize your data and then create a crosstab based on
your normalized data.
 
Thanks Duane here is an example:
March 2003
March 2004
Domestic Factor 3,425.88 4,536.89
Domestic ID 44,243.89 51,457.26
Export Fact 444.89
468.36
Export ID 1,245.09
1,556.67
All CPP 5,679.46
7,659.69

The dates, ie March 2003 and March 2004 are a field called txtmonthlabel
The first column is the field name for thefields on the same line which are
all text fields eg Domestic Factor is the label for txtdomfact, the values
for this field are shown under the appropriate month (txtmonthlabel) and so
on.

So what we are trying to do is to compare the values of the valuea of the
text fields in the first column for the two months shown at the start of the
report.

Is that any clearer?
 
Again, it looks like you might need to normalize your data and then create a
crosstab based on your normalized data.

Your first union query would be something like:
SELECT txtMonthLabel, "Domestic Factor" as WasField, [Domestic Factor] as
SomeValue
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Domestic ID", [Domestic ID]
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Export Fact", [Export Fact]
FROM tblLooksLikeSpreadSheet
UNION ALL
etc...

Then create a crosstab based on this union query with txtMonthLabel as the
column heading, WasField as the Row Heading, and SomeValue as the Value.
 
Sorry Duane but a bit of a newbie here (although you have helped me before)
all that went over my head. I think I need to read up on normalising data and
union queries don't really understand what they are.
Any pointers for examples?
Thanks
Tony

Duane Hookom said:
Again, it looks like you might need to normalize your data and then create a
crosstab based on your normalized data.

Your first union query would be something like:
SELECT txtMonthLabel, "Domestic Factor" as WasField, [Domestic Factor] as
SomeValue
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Domestic ID", [Domestic ID]
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Export Fact", [Export Fact]
FROM tblLooksLikeSpreadSheet
UNION ALL
etc...

Then create a crosstab based on this union query with txtMonthLabel as the
column heading, WasField as the Row Heading, and SomeValue as the Value.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Thanks Duane here is an example:
March 2003
March 2004
Domestic Factor 3,425.88
4,536.89
Domestic ID 44,243.89
51,457.26
Export Fact 444.89
468.36
Export ID 1,245.09
1,556.67
All CPP 5,679.46
7,659.69

The dates, ie March 2003 and March 2004 are a field called txtmonthlabel
The first column is the field name for thefields on the same line which
are
all text fields eg Domestic Factor is the label for txtdomfact, the values
for this field are shown under the appropriate month (txtmonthlabel) and
so
on.

So what we are trying to do is to compare the values of the valuea of the
text fields in the first column for the two months shown at the start of
the
report.

Is that any clearer?
Click to expand...
Click to expand...
 
Roger Carlson has a sample amoung his many
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NormalizeDenormalize.mdb.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Sorry Duane but a bit of a newbie here (although you have helped me
before)
all that went over my head. I think I need to read up on normalising data
and
union queries don't really understand what they are.
Any pointers for examples?
Thanks
Tony

Duane Hookom said:
Again, it looks like you might need to normalize your data and then
create a
crosstab based on your normalized data.

Your first union query would be something like:
SELECT txtMonthLabel, "Domestic Factor" as WasField, [Domestic Factor] as
SomeValue
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Domestic ID", [Domestic ID]
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Export Fact", [Export Fact]
FROM tblLooksLikeSpreadSheet
UNION ALL
etc...

Then create a crosstab based on this union query with txtMonthLabel as
the
column heading, WasField as the Row Heading, and SomeValue as the Value.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Thanks Duane here is an example:
March 2003
March 2004
Domestic Factor 3,425.88
4,536.89
Domestic ID 44,243.89
51,457.26
Export Fact 444.89
468.36
Export ID 1,245.09
1,556.67
All CPP 5,679.46
7,659.69

The dates, ie March 2003 and March 2004 are a field called
txtmonthlabel
The first column is the field name for thefields on the same line which
are
all text fields eg Domestic Factor is the label for txtdomfact, the
values
for this field are shown under the appropriate month (txtmonthlabel)
and
so
on.

So what we are trying to do is to compare the values of the valuea of
the
text fields in the first column for the two months shown at the start
of
the
report.

Is that any clearer?



:

It would help if you showed some actual data with field names. It
looks
like
you might need to normalize your data and then create a crosstab based
on
your normalized data.

--
Duane Hookom
MS Access MVP
--

I apologise for reposting so soon but I wondered whether as I'd sent
a
supplemntal posting and that put a + against the fisrt post then it
may
have
been overlooked it was on a Sunday as well!!!.
So here is my problem
I have a report based on a query. I have a field called
txtmonthlabel
and
a
number of fields that hold numeric data eg txtdata1,
txtdata2,txtdata3
etc
etc. The query finds my data but displays it on the report as:
txtmonthlabel 1
txtdata1, txtdata2,txtdat3 etc etc
txtmonthlabel 2
txtdata1, txtdata2,txtdat3 etc etc

What I want is:
txtmonthlabel 1 txtmonthlabel 2
txtdata1, txtdata1,
txtdata2, txtdata2,
txtdata3 txtdata3
etc etc
etc etc


So the data might look like this:

March 2003 March 2004
Txtdata1 label 2345 5678
Txtdata2 label 46575 45758
Txtdata3 label 454747 64474
etc
etc

I have tried using the column function but that doesn't seem to
work.
Can
someone help?
TIA
Tony Williams
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane as the "Governor" says "I'll be back!"

Duane Hookom said:
Roger Carlson has a sample amoung his many
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NormalizeDenormalize.mdb.

--
Duane Hookom
MS Access MVP
--

Tony Williams said:
Sorry Duane but a bit of a newbie here (although you have helped me
before)
all that went over my head. I think I need to read up on normalising data
and
union queries don't really understand what they are.
Any pointers for examples?
Thanks
Tony

Duane Hookom said:
Again, it looks like you might need to normalize your data and then
create a
crosstab based on your normalized data.

Your first union query would be something like:
SELECT txtMonthLabel, "Domestic Factor" as WasField, [Domestic Factor] as
SomeValue
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Domestic ID", [Domestic ID]
FROM tblLooksLikeSpreadSheet
UNION ALL
SELECT txtMonthLabel, "Export Fact", [Export Fact]
FROM tblLooksLikeSpreadSheet
UNION ALL
etc...

Then create a crosstab based on this union query with txtMonthLabel as
the
column heading, WasField as the Row Heading, and SomeValue as the Value.

--
Duane Hookom
MS Access MVP
--

Thanks Duane here is an example:
March 2003
March 2004
Domestic Factor 3,425.88
4,536.89
Domestic ID 44,243.89
51,457.26
Export Fact 444.89
468.36
Export ID 1,245.09
1,556.67
All CPP 5,679.46
7,659.69

The dates, ie March 2003 and March 2004 are a field called
txtmonthlabel
The first column is the field name for thefields on the same line which
are
all text fields eg Domestic Factor is the label for txtdomfact, the
values
for this field are shown under the appropriate month (txtmonthlabel)
and
so
on.

So what we are trying to do is to compare the values of the valuea of
the
text fields in the first column for the two months shown at the start
of
the
report.

Is that any clearer?



:

It would help if you showed some actual data with field names. It
looks
like
you might need to normalize your data and then create a crosstab based
on
your normalized data.

--
Duane Hookom
MS Access MVP
--

I apologise for reposting so soon but I wondered whether as I'd sent
a
supplemntal posting and that put a + against the fisrt post then it
may
have
been overlooked it was on a Sunday as well!!!.
So here is my problem
I have a report based on a query. I have a field called
txtmonthlabel
and
a
number of fields that hold numeric data eg txtdata1,
txtdata2,txtdata3
etc
etc. The query finds my data but displays it on the report as:
txtmonthlabel 1
txtdata1, txtdata2,txtdat3 etc etc
txtmonthlabel 2
txtdata1, txtdata2,txtdat3 etc etc

What I want is:
txtmonthlabel 1 txtmonthlabel 2
txtdata1, txtdata1,
txtdata2, txtdata2,
txtdata3 txtdata3
etc etc
etc etc


So the data might look like this:

March 2003 March 2004
Txtdata1 label 2345 5678
Txtdata2 label 46575 45758
Txtdata3 label 454747 64474
etc
etc

I have tried using the column function but that doesn't seem to
work.
Can
someone help?
TIA
Tony Williams
Click to expand...
Click to expand...
Click to expand...
 
Back
Top