Question Recording dates (1 Viewer)

[Toolman]

Hello,

I need to prepare my Excel spreadsheets to be imported to Access. The column headers look like this:

DrName Site Discipline Month Year
John Smith North unit PT July 2009

I need to ask Access questions like "how many referrals were there from Jan 2009 to July 2009" and "how many referrals are there Year to Date".

Can Access answer questions like these with the data in the layout above? Or should I make a "Date" column and use a real date - 7/1/09?

Thank you for your help,
Don

 

[Galaxiom]

Access is very good at dealing with dates in date formatted fields. All kinds of things like the day of the week, the time between dates, the quarter etc can be easily found.

While the separate fields might first appear a simple way to get monthly data, the queries will actually be simpler with a single date field.

Also you need to realise that once imported your data should be dismanteld into related tables. Don't think of tables like spreadsheets. It is the biggest mistake made by new developers.

So far I can see there will be a Department table, a Doctor table and a Discipline table each with a primary key and a name. All other tables will only refer to members of these tables by the key belonging to the appropriate record.

You will only have John Smith written once in the whole database in the doctors table. Elsewhere he will be known only by his key. Separate the first and last name of the doc into two fields. This will make it easier to find all doctors called "Smith" for example.

His record might look like: 1 | Smith | John | another fact about John | etc

The data in your spreadsheet will be held in another table. A record in this table might look like: 1 | 5 | 8 | 02/05/2009

You can either massage this data before import by using Excel or during import using Access. If you know how to do it in Excel do it now because this will get you moving faster in Access. But don't do it manually because we can show you how to do it in Access.

 

[Toolman]

Thank you again, Galaxiom,

In the interest of brevity I left out the details. The doctor referral "database" I keep in Excel is already separated into Access like design. The data entry sheet is like I described earlier. But I have separate lists for specialties, doctor name and info (specialty, location, if he/she is on staff, etc), Site Locatilons, disciplines.

And I have already split my one cell "Smith, John" into LastName, FirstName columns. The reason I used Month & Year columns in Excel is that the list of referrals is compiled on monthly basis. So I enter the data for July during the first week of August.

But the date column may come in handy in another way. The report gives the actual date of the referral. While it has not been important in the past to know if the referral happened on the 5th or 25th, I could use it to track trends in the future (more at the end of the month sort of thing).

Again, thank you for your help, and to the others who have helped me with my questions. I know it is all entry level stuff, but it has been a big help to run these ideas by you.

Thanks,