Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Tokes

68 posts

Master Geek


#8573 12-Jul-2006 09:05
Send private message

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


Create new topic
miscellany
4 posts

Wannabe Geek


  #40864 12-Jul-2006 12:07
Send private message

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
Send private message

 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
Send private message

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
Send private message

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

Cheers

Create new topic





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.