Database Tables and Functions

A brief description of some of the most commonly used database tables is given below to assist you with creating either Custom Datasheets or Reports. This is by no means a comprehensive list and you are encouraged to explore the database further before creating your own reports or datasheets.

Children

Most information is found in tblChildren including personal details, parent and/or carer, preferred doctor and any notes. Additional information can be found in tblMedicalNotes, tblImmuSchedule and tblExternalAbsences.

Parents/Guardians

All personal information can be found in tblContacts including address, contact numbers and work details. tblParents contains additional information such as Medicare, ambulance and insurance details as well as credit card and bank account numbers. For details of individual monies owed refer to tblBalances.

Rolls

tblRollTypes contains information pertaining to each room or childcare service offered by a Centre. This includes its type of care, number of places available and fees that apply. tblRollEntries lists all children against their allocated rooms with additional information such as the child's standard fee and daily hours.

Fees

tblFeeTypes contains an entry for each different type of fee used by your organisation. This table indicates where the fees apply and whether they are currently in use (active). Each FeeType has one or more FeeSchedules, details of which can be found in tblFeeSchedules. Here you will find the individual hourly or daily rates for different combinations of number of children and number of days of attendance.

Bookings

tblBookingPatterns contains each booking, relating it to a Parent and a Roll. It provides a start and end date and the booking status eg. placed, waiting etc. tblBookingDays lists each individual daily booking, so there will be an entry for each day that a particular booking applies.

Staff

The primary table here is tblEmployees which contains personal details for each staff member, as well as payment information such as Tax File Number, superannuation details, holiday and sick day allowances. tblCarers contains records for Carers in Family Day Care services, with information including registration number and ABN; carers' personal details are stored in tblContacts.

Accounts

tblAccounts contains an entry for each of the organisation's accounts. tblBanking has an entry for each deposit, including the date, composition of the money paid in and the bank account to which it was paid. tblLedger records all the payments made by Parents to the Centre including method of payment and receipt number.

Organisation

tblServices lists each Centre within your organisation and includes the name, contact details, ABN and Registration Code. Each Centre has the option of belonging to a Group, allowing large organisations to sub-divide their services, for example, by region. These Groups are maintained in tblManageGroups.

Creating Queries for Custom Reports and Datasheets

There are a few things that should be considered when creating queries in order to produce your own custom reports and datasheets.

• Many queries need to include tblTemp (which stores the ID for centres logged into the current session), in order to generate reports across multiple centres. The fields required are Func = 1 and SessionID = GetSessionID(). You should uncheck the Show box in each case as this information is not actually required on the report itself.
• You will see the database contains several tables named lnkXXXXXXX, such as lnkImmuChild. These tables may sometimes be needed in your queries to link together two tables that have no direct link between them. For example, lnkImmuChild links together tblChildren and tblImmuSchedule.
• There are many functions available in the source code that forms part of the database. Below is a brief explanation of some of the more commonly used functions together with sample SQL queries demonstrating their use. (These are very simple queries created for demonstrative purposes only and are unlikely to be useful in their current form.)

GetSessionID() - provides the current session ID (as described above)

Example: SELECT tblServices.Name AS Centre, tblTemp.SessionID FROM tblServices, tblTemp WHERE (((tblTemp.SessionID)=GetSessionID()) AND ((tblTemp.Func)=1));

GetStartDate(), GetEndDate() - provides the start and/or end dates entered by the user which may be used to constrain the report's data

Example: SELECT tblBankAccounts.BankAccountName, tblBanking.BankingDate, tblBanking.BankingTotal FROM tblBankAccounts INNER JOIN tblBanking ON tblBankAccounts.BankAccountID = tblBanking.BankAccountID WHERE (((tblBanking.BankingDate) Between GetStartDate() And GetEndDate()));

AskLongParam("prompt"), AskStringParam("prompt"), AskMoneyParam("prompt") - using these functions in the query criteria prompts the user to enter a particular field to constrain the data. Which one you use will depend on the type of data to be entered.

Example: SELECT tblChildren.ChildFirst, tblChildren.ChildLast FROM tblChildren WHERE (((tblChildren.ChildLast)=AskStringParam("Enter Family Name: ")));

GetCustomData(CustomFieldID, OwnerID) - gets any custom data entered into a Custom Field that may have been set up. tblCustomFieldData will need to be included in your query, so that its CustomFieldID and OwnerID can be passed to the function. Alternatively the CustomFieldID can be obtained from the Custom Field Details screen. The OwnerID will correspond to the ID of the object to which the custom data applies eg. if the Custom Field is attached to Staff, the OwnerID is in fact an EmployeeID.

Example: SELECT tblEmployees.EmployeeID, tblEmployees.FirstName, tblEmployees.LastName, GetCustomData([CustomFieldID],[OwnerID]) AS CustomData FROM tblEmployees, tblCustomFieldData WHERE (((tblCustomFieldData.OwnerID)=[EmployeeID]));