SQL Query to Combine Records

[Clint Marshall]

In AccessXP, I'm looking for the proper way to set up a query that takes a
number of records (rent and CAM amounts for each of several properties) and
combines them into one record for each property.

Ie: combine these records:

Building RentOrCAM Amount
House1 Rent $500
House1 CAM $100
House2 Rent $800
House2 CAM $200
House3 Rent $750
House3 CAM $75

into these records:

Building RentAmt CAMAmt
House1 500 100
House2 800 200
House3 750 75

This feels like some sort of SQL query, though it may just be a grouping
query of some sort.

Can anyone guide me in how to accomplish this?

Thanks!

-Clint Marshall

 

[Michel Walsh]

Hi,


You can run the Crosstab query wizard.

You will Group on Building, Pivot on RentOrCAM, and aggregate with SUM over
Amount.

Hoping it may help,
Vanderghast, Access MVP

 

[MGFoster]

In AccessXP, I'm looking for the proper way to set up a query that takes a
number of records (rent and CAM amounts for each of several properties) and
combines them into one record for each property.

Ie: combine these records:

Building RentOrCAM Amount
House1 Rent $500
House1 CAM $100
House2 Rent $800
House2 CAM $200
House3 Rent $750
House3 CAM $75

into these records:

Building RentAmt CAMAmt
House1 500 100
House2 800 200
House3 750 75

This feels like some sort of SQL query, though it may just be a grouping
query of some sort.

Can anyone guide me in how to accomplish this?

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

Most people would use a cross-tab query. Here's another way:

SELECT Building, Sum(IIf(RentOrCam="Rent",Amount)) As RentAmt,
Sum(IIf(RentOrCam="CAM",Amount)) As CAMAmt
FROM <table name>
WHERE <criteria>
GROUP BY Building

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQyChfoechKqOuFEgEQKKSQCeLF+NZaGZu8aNE1K+/eKMO3Qar/YAoMK2
EYe5s7Ha41ZhoICO8e4vLy6n
=+dcO
-----END PGP SIGNATURE-----