Correct tables for an effective database

[Sillub]

I'm trying to put together a new database based on a previous database put together by someone else. For the most part it isn't bad. It has a table for counties, coordinators, region names, consumer (which contains: fname, lname, address, city, state, zip, DOB, injury date, enrollment date). The problem is, that these consumers typically receive services every year and very often every quarter and the way it has been done in the past is all of the consumer info. table is replicated for each year and saved by year (i.e., roster2009, roster 2010, roster 2011). I would prefer to have one database which uses the same consumer table for each year, but has the ability to run queries that would depict the year and quarter(s) of services received and allow me to track the year(s) and quarter(s) the various individuals received services.



I believe I need to create a service date(year) table and perhaps a quarter table. I'm not sure this is doable and to be honest I took training in Access 2003 nine years ago and have not used the software in several years. Is there a way to do this without programming skills and with my limited knowledge?

[Rod]

I believe I need to create a service date(year) table and perhaps a quarter table.

Seems to me this is a situation where you need the quarter table; you may determine the year from that table. It is also a situation - there are very few - where I would use a meaningful primary key as the values are regular and predictable, say YYYYQ. Whether you need any other attributes in this table depends upon the circumstances; for example, do you need start and end dates? populating this table could be made automatic; for example, a routine that executes at start-up could ensure there are at least two future quarters on the table, the routine adds new quarters as necessary.

OK, here are two possible designs that differ only because the second has a compond key:

ServiceQuarter Integer YYYYQ PK
DateStart Date
DateEnd Date

ServiceYear Integer YYYY PK1
ServiceQuarter Integer Q PK2
DateStart Date
DateEnd Date

Is there a way to do this without programming skills and with my limited knowledge?

You're well away from programming at the moment.

[Rod]

Perhaps I should be a little more detailed. My previous post assumes that the Sevice Quarter table will have additional attributes such as start and end dates. If it doesn't then you should seriously consider whether you need a table at all; maybe it is sufficient to add a YYYYQ attribute to any table that needs it and index this attribute.

[Sillub]

Rod - thanks for your response. I don't have start and end dates - the database only tracks the # of consumers and how often (each quarter) of each year that consumer uses the services over a period of time. For instance, the individual may use the services in 2011 for the 1st, 3rd, and 4th quarter and for 2012 only the 1st and 2nd quarters. I would need to be able to query these numbers/quarters/years for reporting purposes. I'm not 100% sure about what you recommended I do. Make a table with various years and have each year 4 quarters? Or do I have a combo box that shows 4 quarters and I can choose the appropriate quarters for each year? I apologize if I'm a bit slow with this stuff - like I said, I just now trying to get myself back into the ins and out's of Access and the numerous capabilities.

I just saw your second post and think that may work if it will keep the YYYYQ for numerous years/quarters. Will it?
-Renee

[ssanfu]

You might not need to have an additional table. In the Roster table, is there a field for the date when an individuals received services? If so, the year and quarter can be calculated (and not stored).
If you have a field named "ServiceDate" (type Date/Time), in a query there could be two calculated columns:

ServiceYear: DatePart("yyyy", ServiceDate)
ServiceQuarter: DatePart("q",ServiceDate)

[Sillub]

Hi - right now there are 3 columns in the roster showing 2010, 2011, 2012 with 4 quarters under each of those headings. This works, but I know it is not the best way to go about setting this up and it would be better if the personal consumer information was set up in one table and the years (along with the quarters) the individual received services were set up in a separate table. Then a form could be created to allow the end user to enter updated information each quarter if the individual received services and also allow the end user to query based on # of individuals who received services within a specified period of time.
--Renee

[Rod]

Hi Renee

I recommend that the first task is to design your db in such a way that it accurately reflects your data and relationships and moreover is capable of easily producing the reports that you require. Worry about how to implement the user interface later.

From what you describe I propose that the following db fragment meets your requirements.



I suspect the table I call, tblCustomer, already exists. If you offer more than one service then table, tblService, is needed; otherwise you may ignore it and remove the attribute, ServiceID, from table, tblCustomerService.

I prefer to include a meaningless primary key on join/cross reference tables; you could however use a compound primary key of CustomerID + ServiceID + Quarter. Compound keys can become tedious later on in the development cycle but the benefit for you is that the db's referential integrity will prevent duplicate quarters for a customer/service combination.

I assume the attribute, Quarter, to be numeric of format, YYYYQ. You may index this attribute. If you choose the meaningless primary key then you must prevent duplicates programmatically (unless you specify a compound index!). I included the attribute, StatisticalData, to indicate that there might be other information you wish to store about a customer service incident.

If you are using, in the user interface, actual dates then ssnafu's comments are relevant; otherwise simple arithmetic manipulation can code and decode the YYYYQ format.

Predicting the future (wish I had such a talent) you may end up with a drop down combox box for selecting the year and a multi-select list box for choosing any combination of the quarters.

Time has come I think for you to describe in a little more detail what you have and where you are in the overall scheme of things. You mention a new database and I have assumed you are starting with a blank sheet of paper. Yes, of course, it is probably expedient to copy those parts of the existing system into your new project. Actually if you expect to keep more than one third of the existing system then my approach would be to copy the lot and gradually delete those parts I did not wish to keep. This is a more controlled approach.