Hi... Have a slight problem with a database I am making...
I think I am missing something really obvious!
I am creating a database, which will generate invoices for money owed by clubs in a sports league.
I want to be able to generate monthly invoices, for each club, which adds up the number of members joined their club, and the total price.
Here is an outline of the process that the database will need to be used for:
When we get a registration form, we will need to enter the following into the invoice database:
Club Name
Amount (£)
There will be invoices, that are sent to each club so they know what they need to pay. These will need to show the following:
The number of registrations recieved between a certain time period (eg. monthly)
The total price this club owes for these registrations during this period.
I will also need to be able to print off statements which show what each club has paid and when, and what may still be outstanding.
I also need to be able to chase non-payments, so a list to show who has not paid yet would be helpful.
I have tried out various different structures to find one that works, and have become stuck!
So far, this is the format that I have:
TblClub:
ClubID *
ClubName
TblOutstanding:
OutstandingID *
ClubID (linked to tblClub)
Amount
Date
Invoiced (Y/N)
Paid (Y/N)
Notes
I have queries to list which registrations have, or have not been paid for; or have or have not been invoiced. So generating a report to list registrations that have not paid yet is not a problem.
The problem is, I have a form related to one of these queries (which shows clubs that have not been invoiced yet). This lists this information correctly, but are all separate records. I cannot work out how to choose one club and see all the registrations that this club has not paid for... And I cannot find a way to make a report where you choose the club and see all the registrations non-invoiced yet.
I keep trying different things, none of which seem to work! I am wondering if I am thinking about this in the wrong way, and wondered if you had any advice on how I could structure the database to make it work!
Any ideas please reply, if not no worries lol
xxxx
Hi,
First question - do you have a table , linked to club table which records members and their join date ? If so you can have a query which will pick all the members who have joined that month and generate an invoice.
Denis
It looks as though you haven't really considered all of the information you are interested in. You have seemingly vague suggestions of requirements, but you are missing so much more. What data needs to be on the invoice, how often are the clubs invoiced, how much information do you keep on the club, how much information on the people? You need to list all of this data and then normalise it. The normalisation process will give you the table structure that you need and will document the relationships between the different entities.
For basic assistance on normalisation, your favourite search engine is your best friend. :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.