How to obtain a spreadsheet-like table - Rookie

[Marcus]

Activity: house rentals; a contract is agreed with tenants that (should) pay
on a monthly basis; for history/tracking purposes, monthly due fees are
stored in a table that represents "periods" (terms\cycles); each contract
can have multiple periods, and each period holds begin\end date and due
fees; once a fee increase occurs, a new period is created holding new due
fees. Payments made are stored in another table.

Example:
Building A
Apartment 1
Contract M
Period 1 - begin, end, monthly due fee X
Period 2 - begin, end, monthly due fee Y
.........
Contract N
Period 1 - begin, end, monthly due fee XX
Period 2 - begin, end, monthly due fee YY
.........
Apartment 2
..........

The database holds data about buildings, apartments (many per building),
contracts (many per apartment), periods (many per contract), monthly
payments (many per period)

Given the following structure (I'm leaving out fields not envolved in the
discussion):

TABLE_BUILDINGS
id_building
description_building

TABLE_APARTMENTS
id_apartment
id_building
description_apartment

TABLE_CONTRACTS
id_contract
id_apartment

TABLE_PERIOD
id_period
id_contract
monthly_due_fee

TABLE_PAYMENTS
id_payment
id_period
date
monthly_amount_payed

Would like to obtain a spreadsheet like table for charting purposes; if
possible show data with a stacked line pivotchart (otherwise export data to
excel), to show "monthly_due_fee" trends; stacked line chart needeed to
higlight each apartment/fee trend.

View/Query layout.

The first row holds date descriptions (in a year/month fashion):

[empty] date1(year/month) date2(year/month) ........
dateX(year/month)

Following rows hold: building/apartment description and monthly payments:

Building A - Apartment 1 fee_date1 fee_date2 .....
fee_dateX
Building B - Apartment 2 fee_date1 fee_date2 .....
fee_dateX
.......
Building Z - Apartment zz fee_date1 fee_date2 .....
fee_dateX

No data about contracts and period is shown, just apartaments and fees.

I'm wondering if I can obtain such a result with a query (I'm accessing a
SQL server) or do I need to VBscript?

Thanks for helping

 

[MGFoster]

Use a cross-tab query; also known as a pivot table. You can also make a
pivot chart from the query, once you get it running as you want.

 

[Marcus]

I'm looking for it but it looks like it doesn't work with .adp
What can I try?

Use a cross-tab query; also known as a pivot table. You can also make a
pivot chart from the query, once you get it running as you want.
--
MGFoster:::mgf00
Oakland, CA (USA)

Activity: house rentals; a contract is agreed with tenants that (should)
pay on a monthly basis; for history/tracking purposes, monthly due fees
are stored in a table that represents "periods" (terms\cycles); each
contract can have multiple periods, and each period holds begin\end date
and due fees; once a fee increase occurs, a new period is created holding
new due fees. Payments made are stored in another table.

Example:
Building A
Apartment 1
Contract M
Period 1 - begin, end, monthly due fee X
Period 2 - begin, end, monthly due fee Y
.........
Contract N
Period 1 - begin, end, monthly due fee XX
Period 2 - begin, end, monthly due fee YY
.........
Apartment 2
..........

The database holds data about buildings, apartments (many per building),
contracts (many per apartment), periods (many per contract), monthly
payments (many per period)

Given the following structure (I'm leaving out fields not envolved in the
discussion):

TABLE_BUILDINGS
id_building
description_building

TABLE_APARTMENTS
id_apartment
id_building
description_apartment

TABLE_CONTRACTS
id_contract
id_apartment

TABLE_PERIOD
id_period
id_contract
monthly_due_fee

TABLE_PAYMENTS
id_payment
id_period
date
monthly_amount_payed

Would like to obtain a spreadsheet like table for charting purposes; if
possible show data with a stacked line pivotchart (otherwise export data
to excel), to show "monthly_due_fee" trends; stacked line chart needeed
to higlight each apartment/fee trend.

View/Query layout.

The first row holds date descriptions (in a year/month fashion):

[empty] date1(year/month) date2(year/month) ........
dateX(year/month)

Following rows hold: building/apartment description and monthly payments:

Building A - Apartment 1 fee_date1 fee_date2 .....
fee_dateX
Building B - Apartment 2 fee_date1 fee_date2 .....
fee_dateX
.......
Building Z - Apartment zz fee_date1 fee_date2 .....
fee_dateX

No data about contracts and period is shown, just apartaments and fees.

I'm wondering if I can obtain such a result with a query (I'm accessing a
SQL server) or do I need to VBscript?

Thanks for helping

 

[Marcus]

I've read about crosstab store procedures. But i'don't need to summarize...
is it okey?

Use a cross-tab query; also known as a pivot table. You can also make a
pivot chart from the query, once you get it running as you want.
--
MGFoster:::mgf00
Oakland, CA (USA)

Activity: house rentals; a contract is agreed with tenants that (should)
pay on a monthly basis; for history/tracking purposes, monthly due fees
are stored in a table that represents "periods" (terms\cycles); each
contract can have multiple periods, and each period holds begin\end date
and due fees; once a fee increase occurs, a new period is created holding
new due fees. Payments made are stored in another table.

Example:
Building A
Apartment 1
Contract M
Period 1 - begin, end, monthly due fee X
Period 2 - begin, end, monthly due fee Y
.........
Contract N
Period 1 - begin, end, monthly due fee XX
Period 2 - begin, end, monthly due fee YY
.........
Apartment 2
..........

The database holds data about buildings, apartments (many per building),
contracts (many per apartment), periods (many per contract), monthly
payments (many per period)

Given the following structure (I'm leaving out fields not envolved in the
discussion):

TABLE_BUILDINGS
id_building
description_building

TABLE_APARTMENTS
id_apartment
id_building
description_apartment

TABLE_CONTRACTS
id_contract
id_apartment

TABLE_PERIOD
id_period
id_contract
monthly_due_fee

TABLE_PAYMENTS
id_payment
id_period
date
monthly_amount_payed

Would like to obtain a spreadsheet like table for charting purposes; if
possible show data with a stacked line pivotchart (otherwise export data
to excel), to show "monthly_due_fee" trends; stacked line chart needeed
to higlight each apartment/fee trend.

View/Query layout.

The first row holds date descriptions (in a year/month fashion):

[empty] date1(year/month) date2(year/month) ........
dateX(year/month)

Following rows hold: building/apartment description and monthly payments:

Building A - Apartment 1 fee_date1 fee_date2 .....
fee_dateX
Building B - Apartment 2 fee_date1 fee_date2 .....
fee_dateX
.......
Building Z - Apartment zz fee_date1 fee_date2 .....
fee_dateX

No data about contracts and period is shown, just apartaments and fees.

I'm wondering if I can obtain such a result with a query (I'm accessing a
SQL server) or do I need to VBscript?

Thanks for helping

 

[MGFoster]

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

Create a query that gets all the data that you need in the PivotTable
(cross-tab) query. While in query design view: on the Access menu bar
select View > Pivot Table View and put the column names in the
appropriate positions on the Pivot Table dialog box. Save the query.
When you run the query the pivot table will be displayed.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR5fv4IechKqOuFEgEQLnPwCgmdnXalBUY6lkPzQCIi0PD8eQOSIAoPld
zT7cn7kRInSO75bzb4GgPrGm
=GmJr
-----END PGP SIGNATURE-----

 

[Marcus]

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

Create a query that gets all the data that you need in the PivotTable
(cross-tab) query. While in query design view: on the Access menu bar
select View > Pivot Table View and put the column names in the
appropriate positions on the Pivot Table dialog box. Save the query.
When you run the query the pivot table will be displayed.
--

I've followed what you've suggested, and it took a while to get the proper
layout, but in the end I've got it!

The problem now is that, although setting the view property to "PivotTable"
when designing the Query\View, once saved and launched again, data is showed
in datasheet view!

Worst of all is that once in datasheet view and select pivottable view, the
layout is empty and need to start again from scratch...
the same happens when switching from pivottable view to pivotchart. All
empty.

Is this beheaviour by design?

 

[MGFoster]

When you make the changes to the query you're supposed to save the query
design again. Then the output is supposed to be formatted as you have
saved.

 

[Marcus]

I do, but it doesn't change the beheaviour: the pivottable is empty and you
have to place fields into it.... Access 2003 + SP3.....

 

[MGFoster]

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

In my version, if I run the query again it shows in datasheet view -
then when I select View > PivotTable it shows in my saved PivotTable
view. I don't know why it doesn't do the same w/ your version. (My
version of Access: 2003).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR5peZ4echKqOuFEgEQKCAQCg4dP46pu6s6h6KsN8fhfjrk4EkHYAoKZu
gxL3hkAfhDSqX+pwSwmcYpEY
=vnIl
-----END PGP SIGNATURE-----