How to obtain a spreadsheet-like table - Rookie

M

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
 
M

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.
 
M

Marcus

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


MGFoster said:
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
Click to expand...
Click to expand...
 
M

Marcus

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



MGFoster said:
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
Click to expand...
Click to expand...
 
M

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-----
 
M

Marcus

MGFoster said:
-----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.
--
Click to expand...

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?
 
M

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.
 
M

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.....
 
M

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-----
 

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

Excel function calculations used in MS Access query 1
How do I use Excel 2007 to determine late payment amounts 3
database to store data entered from a spreadsheet? 4
APR Question 11
Here's How To Make Thousands Every Month 0
phony French doc defrauding holistic healthcare practitioners via web 0
How Microsoft Office Tech Support (In Reality Convergys of Ohio) is a Miserable Failure and Oxymoro 0

Top