MS Access guru please help!

Forums › Microsoft Windows › MS Access guru please help!

Tokes

68 posts

Master Geek

#8573 12-Jul-2006 09:05

Can anyone help? I'm not really up to play with Access - and need to simplify a database that we have for work. We record a list of attendances, and have check boxes in Access that we mark depending on the day that a client works with us.

What would be ideal would be able to create a form that has drop down boxes. One for month - one for year, and then all the check boxes for the days of the week. At the moment we have 52 rows of check boxes - and there must be an easier way! It is like we need some IF statements for the data type in the table?

Please can someone with a bit of Access know-how please help!

Cheers

PeterReader

Affiliate link: Shop computers and accessories at Lenovo.

miscellany

4 posts

Wannabe Geek

#40864 12-Jul-2006 12:07

Tokes,

You can certainly simplify what you are doing there at the moment.

Is the design of your tables set in concrete, or could you consider a review of this aspect? Either way, could you post back with some details of how you have this set up at the moment, se I can see what we are working with?

Is there any reason why you don't simply enter the date of the Attendance?

Tokes

68 posts

Master Geek

#40873 12-Jul-2006 13:00

Thanks for the reply. Having a simple interface that our tutors can enter in attendance data by month is beneficial, as they aren’t the most computer literate crowd in the world!

What we have at the moment is by month (not by week which I stated earlier – sorry!). We enter in the number of attendances in each department for the month and then add up the total (is there a way that we can automatically sum total the other columns for this field?). So – for the month of Apr we have for each client (client number is the primary key) the total number of times they used each one of the 6 departments. The form that we are currently using has each month and department – in one great big list down the page! The design definitely isn’t set in concrete.

Ideally I would like drop-down lists – so you would select 2006 (for the year), April (for the Month) – and then just enter in the numbers for each department according to the actual attendances (so have 6 number boxes visible on the form instead of 72!). This would then populate the appropriate columns in the table. Is there a way to do this with dropdowns in Access? It is like the year and month need to be used as part of a lookup??

We need to keep each month’s data separate for reporting purposes. This is why we can’t just record the date – as we need to be able to report on how many times a month certain people used each department.

miscellany

4 posts

Wannabe Geek

#40883 12-Jul-2006 13:55

Tokes,

You are focussing on the user interface (form), but for me this always comes secondary to understanding the data relationships (tables).

Ok, so you are not recording each individual attendance event? Only monthly summary figures for each client?

If so, I would recommend constructing your tables something along these lines...

Table: Clients
ClientID
ClentName
Address
whatever else

Table: Departments
DepartmentID
DepartmentName
whatever else

Table: Attendances
AttendanceID
ClientID
AttendanceYear
AttendanceMonth
DepartmentID
Attendances

I believe this most accurately models the data you are working with.

You can make a form based on the Clients table, and a continuous view form based on the Attendances table. You can place the Attendances form on the Clients form as a subform. You can look at the record for any given client, and view/enter their attendance tallies data.

And (not or!), you can make a form based on the Departments table, and a slightly different continuous view form based on the Attendances table. You can place the Attendances form on the Departments form as a subform. You can look at the record for any given department, and view/enter its attendance tallies data.

And (not or!), you can make an (again) slightly different continuous view form based on the Attendances table. You can use this form for bulk entry of attendance tally data.

What you are referring to as drop-down lists are called comboboxes. On the form for the Attendances, you can have a combobox for the DepartmentID, with its Row Source property set to the Departments table, such that the DepartmentID is the value recorded in the underlying Attendances table, but the Department Name is what is shown in the combobox on the form (more humen-friendly). Similarly, you can have a combobox for the ClientID, with its Row Source property set to the Clients table, such that the ClientID is the value recorded in the underlying Attendances table, but the Client Name is what is shown in the combobox on the form. These comboboxes can be set up to Auto Complete if you enter the beginnings of the department/client names.

It would certainly be possible to also use comboboxes for the entry of the AttendanceYear and AttendanceMonth (Note: you should not use the words 'year' and 'month' to name these fields or controls - these are 'reserved words' (i.e. have a special meaning) in Access). But I would personally not go down this track. It is probably just as quick/easy to type a number in these controls on the form, as it is to select an item from a combobox's list. And it is also very easy to set your form up so that the value entered into a field is automatically entered by default into the next new record, which may be ideal in terms of your usage.

Tokes

68 posts

Master Geek

#40889 12-Jul-2006 15:17

Great! Thanks a lot for that. I will work through it tonight and see how it all works out.

Cheers