Report Theory

  • Thread starter Thread starter Cyndi
  • Start date Start date
C

Cyndi

Ok here is my problem

I have tables that keep up with clients, horses, and payments

I need a report that pulls the client information, horse information,
and payment infor

what I am having problems with is that some people pay in more than
one payment and I want them combined on one line but some horses have
multiple owners and I need these on seperate lines I have tried
grouping by several different ways but it doesnt work could someone
please put me in the right direction theory wise what I need to do??

All help appreciated greatly
 
Hi,

I don't know exactly how your business works, or how your database is
currently setup.
I have attached a possible layout for a database that would give you a fair
amount of flexability as to how you grouped your reports etc.

These tables could obviously be extended, eg the Horse table could have
size, colour etc fields, the Customer table would probably have address,
phone number etc.

The key is the Horse Ownership table which contains details of which owners
own which horses, and allows an owner to own more than one horse, or a horse
to have more than one owner.

Horse Table
=========
HORSE_ID HORSE_NAME
1 Dobbin
2 Patch
3 Star


Customer Table
===========
CUSTOMER_ID CUSTOMER_NAME
1 Karen
2 Sue
3 Ben


Horse Ownership Table
================
HORSE_ID CUSTOMER_ID
1 1 - Dobbin is owned by Karen
2 1 - Patch is also owned by Karen
2 2 - ... and also by Sue
3 3 - Star is owned by Ben


Payments Table
===========
PAYMENT_DATE PAYMENT_AMOUNT CUSTOMER_ID HORSE_ID
1/1/02 50 1
1 - Karen paid £50 for Dobbin
2/1/02 75 1
1 - Karren paid 75 for Dobbin
3/1/02 25 1
2 - Karren paid 25 for Patch
4/1/02 45 2
2 - Sue paid 25 for Patch


You could group all payments for a horse, or all payments for a horse split
by owner, or even all payments by owner split by horse etc.

Hope this is some help.

ChrisM
 
if this primarily used to track payments and owners I miay suggest that you consider usiing somehting other than Access and put the same information in QuickBooks. It will track partial payments and generate excellent reports. Why recreate the whell if one already exists. Good luck.
 
Because each payment entered into the payments table contains the Customer
and the Horse Id, you could do (say)

List total of all payments by horse.
select HORSE_NAME,sum(PAYMENT_AMOUNT)
from PAYMENTS_TABLE,HORSE_TABLE
where HORSE_TABLE.HORSE_ID = PAYMENTS_TABLE.HORSE_ID
group by HORSE_NAME

Dobbin 125
Patch 50

List payments by horses split by customer:

select HORSE_NAME,CUSTOMER_NAME,sum(PAYMENT_AMOUNT)
from PAYMENTS_TABLE,HORSE_TABLE,CUSTOMER_TABLE
where HORSE_TABLE.HORSE_ID = PAYMENTS_TABLE.HORSE_ID
and CUSTOMER_TABLE.CUSTOMER_ID = PAYMENTS_TABLE.CUSTOMER_ID
group by HORSE_NAME,CUSTOMER_NAME.

Dobbin Karen 125
Patch Karen 25
Patch Sue 25

If you were doing this as a report, then you could also generate sub-totals
by horse:

eg.

PAYMENTS REPORT
================
Dobbin Karen 125

Subtotal for Dobbin: 125
-------------------------
Patch Karen 25
Patch Sue 25

Subtotal for Patch: 50
-------------------------
Grand Total of Payments: 175

Cheers,

ChrisM
 
Back
Top