Selecting records with certain characteristics

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the table Data I've the following primary key;
Cost Unit-Category-Period-Account which create a unigue record for the
field Amount. Three categories exist; Budget, Forecast and Outcome. I have
created a parameter query where Between [From Period] And [To Period] and
Between [From Cost Unit] And [To Cost Unit] is used and now I want to display
Budget, Forecast and Outcome in separate columns. So I've created colums
based on the table Data and the field Amount and now I want the query to
summarize the Budget, Forecast and Outcome separately in the three columns
I've created. However I've been unable to get Access to perform this "split"
and I keep getting the sum of B,O, & F in each column.

Any suggestions, or alternative solutions?

Best regards / Bell
 
Bell said:
In the table Data I've the following primary key;
Cost Unit-Category-Period-Account which create a unigue record for the
field Amount. Three categories exist; Budget, Forecast and Outcome. I have
created a parameter query where Between [From Period] And [To Period] and
Between [From Cost Unit] And [To Cost Unit] is used and now I want to display
Budget, Forecast and Outcome in separate columns. So I've created colums
based on the table Data and the field Amount and now I want the query to
summarize the Budget, Forecast and Outcome separately in the three columns
I've created. However I've been unable to get Access to perform this "split"
and I keep getting the sum of B,O, & F in each column.

Any suggestions, or alternative solutions?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL View:

SELECT
Sum(IIf(Category="B",Amount)) As Budget,
Sum(IIf(Category="O",Amount)) As Outcome,
Sum(IIf(Category="F",Amount)) As Forecast
FROM [Data]
.... etc. ...

Or, in the design grid:

Field: Budget: IIf(Category="B",Amount)
Total: Sum

Ditto for other fields, just change the letter to match the category
name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQvEovoechKqOuFEgEQKK7wCfUSkbNjnCxNCn+PTCEOdfyeUmPnMAoJPd
uvGRY2NY2ZRI9fbjGs99H+vW
=C65N
-----END PGP SIGNATURE-----
 
Thank you MGFoster, it worked perfectly.

"MGFoster" skrev:
Bell said:
In the table Data I've the following primary key;
Cost Unit-Category-Period-Account which create a unigue record for the
field Amount. Three categories exist; Budget, Forecast and Outcome. I have
created a parameter query where Between [From Period] And [To Period] and
Between [From Cost Unit] And [To Cost Unit] is used and now I want to display
Budget, Forecast and Outcome in separate columns. So I've created colums
based on the table Data and the field Amount and now I want the query to
summarize the Budget, Forecast and Outcome separately in the three columns
I've created. However I've been unable to get Access to perform this "split"
and I keep getting the sum of B,O, & F in each column.

Any suggestions, or alternative solutions?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In SQL View:

SELECT
Sum(IIf(Category="B",Amount)) As Budget,
Sum(IIf(Category="O",Amount)) As Outcome,
Sum(IIf(Category="F",Amount)) As Forecast
FROM [Data]
.... etc. ...

Or, in the design grid:

Field: Budget: IIf(Category="B",Amount)
Total: Sum

Ditto for other fields, just change the letter to match the category
name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQvEovoechKqOuFEgEQKK7wCfUSkbNjnCxNCn+PTCEOdfyeUmPnMAoJPd
uvGRY2NY2ZRI9fbjGs99H+vW
=C65N
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top