Jacinda:
Firstly its important to ensure that the tables in the database
correctly model the real world situation which the database is
modelling. Tables represent entity type, and their columns (fields)
represent attributes of those entity types.
Clearly you'll start with a Children entity type, and therefore a
column of that name with columns Firstname, Lastname, DoB etc. Of
course the customer will be the child's carer, usually a parent, but
not necessarily so, so you'll want a table Carers, with columns
FirstName, Lastname, and other columns for address data, phone data
etc.
You now have to consider whether each child will have just one carer
in the database or whether you need to cater for more than one carer
per child. If the former its simply a case of including a CarerID
column in the Children table to reference the primary key CarerID
column of the Carers table. Don't use names as keys BTW as they can
be duplicated.
If the database is to record more than one carer per child then the
relationship between Children and Carers is many-to-many in that each
child can have one or more carers, and each carer can be responsible
for one or more children. A many-to-many relationship is modelled by
a separate table which has two foreign key columns, CarerID and
ChildID referencing the primary keys of the two referenced tables. In
this scenario you might want to indicate which of the carers per child
is the primary carer, so you'd add a Boolean (Yes/No) column
PrimaryCarer to this ChildCarers table.
For attendances you'd need something like a Sessions table with one
row per 'session' (presumably a whole day as this is 'daycare') This
needs a SessionDate column of course, which as its unique in the table
can be its primary key. You might or might not need other columns to
record attributes of a 'session'; you'll be able to judge that better
than we can.
The recording of attendances is itself a many-to-many relationship
between Children and Sessions so an Attendances table is needed with
columns ChildID and SessionDate referencing the primary keys of the
Children and Sessions tables.
As you are charging on the basis of weeks allocated per child, not on
attendances, then the Attendances table is not a basis for triggering
the weekly charge (an invoice in accounting terms). So you need some
other means of 'registering' a child for a number of weeks. Another
table is therefore called for, Registrations say, with columns
ChildID, DateFrom, DateTo and WeeklyFee, plus columns for any other
attributes of the 'registration' of a child which might be required.
You now a means to store each weekly 'invoice' per child, so an
Invoices table is required with columns ChildID, InvoiceDate,
InvoiceAmount, Paid. There are a few points to note about this table:
(a) InvoiceDate will be the Monday of each week within the range
determined by the DateFrom and DateTo values in the Registrations
table for the child; (b) InvoiceAmount is derived from the WeeklyFee
value for that child, but note that it does not introduce redundancy
to store the derived value in the Invoices table. This is because,
over an extended period, you might change the fee at some time, so if
the fee were simply looked up from the Registrations table the
previous invoices would become incorrect representations of the
amounts charged when a fee is amended. By getting the current fee
form Registrations and storing it in each invoice record, however, the
values in the previous invoices are unaffected; (c) Paid could be a
Boolean (Yes/No) column, or it could be the date paid.
I realize that I haven't as yet addresses question, but it is vitally
important that the 'logical model' is sound before considering other
aspects of the application. So, what you need is a routine which will
insert a row into the Invoices table for every week per child within
the range determined by the values for that child in the registrations
table. The simplest way to do this would be to generate all the
invoices for the range in advance at the time of registration of each
child. It would then be a case of retrieving all invoice records
every week where the date is on or before the Monday of that week and
are unpaid (either Paid = False if Boolean, or Is Null if the date
paid). The generation of the invoices requires the execution of an
'append' query, which can be done in the AfterInsert event procedure
of a form based on the Registrations table.
You firstly need to identify all the Monday dates within the date
range, and the best means of doing this is to have a MondaysCalendar
table, which is simply a table of all Monday dates, in a column
MondayDate say, over a period, e.g. from a year ago to 10 years in the
future. You can easily create one by serially filling down a column
in Excel and importing it into Access as a table, or I can let you
have some VBA code for doing it. So, armed with the MondaysCalendar,
table the code to generate the invoices would be like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
strSQL = "INSERT INTO Invoices(InvoiceDate, ChildID,
InvoiceAmopunt)" & _
" SELECT MondayDate, " & Me.ChildID & "," & Me.WeeklyFee &
_
" FROM MondaysCalendar WHERE MondayDate" & _
" BETWEEN #" & FORMAT(Me.DateFrom,"yyyy-mm-dd") & _
"# AND FORMAT(Me.DateFrom,"yyyy-mm-dd") & "#"
cmd.CommandText = strSQL
cmd.Execute
Each week you can return the unpaid invoices up to and including the
current date with a query like this if Paid is a Boolean column:
SELECT *
FROM Invoices
WHERE NOT Paid
AND InvoiceDate <= DATE();
or if Paid is a date:
SELECT *
FROM Invoices
WHERE Paid IS NULL
AND InvoiceDate <= DATE();
You'd in fact also join the Children, Carers (and ChildCarers if
recording multiple carers per child) in this table so that you can
return the child and carers names. If including a ChildCarers table
you'd also restrict the query to where PrimaryCarer = TRUE to return
only the primary carers for invoicing purposes. An invoice report can
be based on this query.
Because all the invoices are being generated in advance, and pulled
out only when they become active, you'd need to include provision for
deleting the redundant rows from the Invoices table in the event of a
child being withdrawn prior to the final date to which they'd been
originally registered. One way would be by a Delete query which
restricts the rows deleted to these with invoice dates on or after the
current date, which could be executed from any form which includes a
control with the ChildID as its vale, so this could be a bound form or
an unbound dialogue form with a combo box listing the children. The
code would be like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
strSQL = "DELETE * FROM Invoices" & _
" WHERE ChildID = " & Me.ChildID & _
" AND InvoiceDate >= DATE()"
cmd.CommandText = strSQL
cmd.Execute
Ken Sheridan
Stafford, England
