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 »

Nanoleaf enhances lighting line with launch of Triangles and Mini Triangles
Posted 17-Oct-2020 20:18


Synology unveils DS1621+ 
Posted 17-Oct-2020 20:12


Ingram Micro introduces FootfallCam to New Zealand channel
Posted 17-Oct-2020 20:06


Dropbox adopts Virtual First working policy
Posted 17-Oct-2020 19:47


OPPO announces Reno4 Series 5G line-up in NZ
Posted 16-Oct-2020 08:52


Microsoft Highway to a Hundred expands to Asia Pacific
Posted 14-Oct-2020 09:34


Spark turns on 5G in Auckland
Posted 14-Oct-2020 09:29


AMD Launches AMD Ryzen 5000 Series Desktop Processors
Posted 9-Oct-2020 10:13


Teletrac Navman launches integrated multi-camera solution for transport and logistics industry
Posted 8-Oct-2020 10:57


Farmside hits 10,000 RBI customers
Posted 7-Oct-2020 15:32


NordVPN starts deploying colocated servers
Posted 7-Oct-2020 09:00


Google introduces Nest Wifi routers in New Zealand
Posted 7-Oct-2020 05:00


Orcon to bundle Google Nest Wifi router with new accounts
Posted 7-Oct-2020 05:00


Epay and Centrapay partner to create digital gift cards
Posted 2-Oct-2020 17:34


Inseego launches 5G MiFi M2000 mobile hotspot
Posted 2-Oct-2020 14:53









Geekzone Live »

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


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



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.

Alternatively, you can receive a daily email with Geekzone updates.