I have a db that has calculated when couseling is to happen based on a set date and a specific time period. Example: contract end date= 120 day couseling, 90 day couseling, etc. Ineed to know who has couseling in the current month.
-
-
Hi Tom. If you could provide some sample data from your tables and queries and explain what you really want us to look at for you this would help us to help you. Members are unlikely to download a zipped file 'cold' with no clue as to what it might (or might not) contain, particularly as such files have the potential to be a security risk for the recipient!
-Stewart -
My data looks like this:
Name: ETS: Days to ETS: Date of 270 Couseling: Date of 180 Couseling...etc
Every month I need to give couseling but depending on how far out a soldier is from his ETS date tells me who does the couseling. I have to setup the time and place for this to happen. I am looking for a report/query that will tell me who has what counseling this month. Thanks for the quick response.Comment
-
Hi Tom. If the data you show above is stored in a table rather than being calculated as needed this tells me straight away that your table structure is not normalised (that is, in an optimal relational form in which separate entities are in separate tables).
Reason is that your counselling dates form a repeated group, something which is taken out by normalisation to first normal form (see our insights article on database normalisation and table structures for further details).
Anyhow, for each person I reckon you will need to record their attended counselling sessions in a separate table which records the date of the counselling session attended along with other pertinent fields, such as notes on the outcome and so on. You can then query this table to find the last-attended counselling date to provide the current number of days since last counselled. You'll then need to apply rules that you'll need to define to ensure that you identify people within the intervals necessary to meet counselling dates.
If, as I suspect, the dates are hard-coded as columns in a non-relational table it is virtually impossible to work on them with a relational database. The attribute is really just one item - date of next counselling session - and the rows should be telling us what instances there have been of these counselling sessions. Instead, the attributes are separated column-wise within one row - very hard to do anything with unless you are working non-relationally in Excel, say.
-StewartComment
-
So I should create a table for each couseling date and then query each of those tables to get my report?Comment
-
No! This ONE table is storing the details of counselling sessions, plural. Each row in that table represents attendance at a counselling session by a person in your list. If you hold, say, 50 counselling sessions in a year then there will be 50 rows at least added to that table each year. There will not be 50 separate counselling tables...
The counselling table has a one-to-many relationship with your Person details table.
All of this is explained in the Insights article I have recommended, or in any basic text on relational data design.
To put it in a plainer form:
You want to store details about people who attend counselling sessions. For each counselling session you will record the ID of the person counselled, the counselling date, the name or the id of the counsellor, a note of the outcome of that session, and so on.
For each table, the items you want to store become the attributes - the fields - of the table.
It is absolutely crucial to successful development that you understand the way tables relate to each other - I cannot stress this enough!
-StewartComment
-
OK, so I think I didn't form my question correctly. I need to be able to pull a report that tells me who needs couselling for this month. I will pull this report once a month. I am not storing IF the couselling is donw only when it is due.
I have one table that has the basic info: name, rank, job title, ETS date. From there I created qurries for the specific couselling dates and one to calculate the number of days till the ETS date. So based on the insight article you suggested, I am not normal.
Do I need one more table with the couselling dates instead of it being JUST a query? If I do make it a table, how do I get the monthly report from it?
Again thank you for your assistance!Comment
-
You definitely need a table that will store for each person the details of the counselling sessions attended. At its simplest this table could consist of nothing more than two attributes - the persons service number (their name is not unique and is not of itself sufficient to identify individuals within the table), and the date of the counselling session attended.
You will then be able to query this table to determine when was the most recent counselling session for each person, and in turn how many days have elapsed since the last session attended (as mentioned in earlier posts). You do not have enough information if you rely on the dates on which counselling is supposed to happen, as for a number of reasons people will often not be able to attend on the dates assumed by a calculation based on rigid intervals.
-StewartComment
-
Make sense now. Thanks a million for helping me with this!Comment
Comment