Table help

[jcaptchaos2]

I have created a database to track start and stop times, the column headers are (Number, Time On, Time Off, and date) I need help with 2 things, the first I need to know if there is a way to only allow one on time and one off time for that date, I know you can set columns to no duplicates but in this case it will not work. I need some how to combine the number and date together then have it not let you enter again. The second question is I want to keep most of this in Access and am wondering if I can query the table to pull the number, date, Time On, time Off and then the difference between the on and off time. I can pull it into Excel and do it but again I would like to keep it in Access.

 

[dk]

You can ensure that there are no duplicated date/number combinations by making those fields the primary key or by making a unique index based on those columns. In design view just highlight those two fields and then click on the Key icon to make that the primary key.

You should be able to query the data but it depends on how your Time On and Time Off fields are defined e.g. are they Date/Time, Number or something else. Assuming that they are DateTime then something like this would do the trick:

SELECT THE_DATE, THE_NUMBER, Round(([TIME_OFF]-[TIME_ON])*24*60,0) AS NUMBER_OF_MINUTES FROM THE_TABLE

Although if you give us more info about your field/table names and data types then we'd be able to get more specific.

HTH
DK

 

[jcaptchaos2]

DK,

Thanks for your help, I ended up splitting the on / off tables into two different tables and now I am able to query the tables and make reports like I wanted to, I also used your suggestion on the primary keys and it worked like a charm. I am not a big Access guy but have been tasked with a couple of big projects and thanks to you all on this site I am making progress. I am sure that I will have lots of questions this week and as I have a Friday dead line, Thanks again for your help.

 

[jcaptchaos2]

DK,

I guess the primary key did not work as I wanted, by setting them both as primary it will only let me enter one record for that date, i tried combining the number and date in the table and thought maybe I could set that as a primary key but it will not let me, maybe I don't fully understand your advice.

 

[jcaptchaos2]

Ok I think I figured out the unique index part and have it working thanks again.

 

[dk]

You can set the primary key by going into design view, selecting both of the fields (if they're not next to each other then you can use Ctrl + click). Once they are selected you just press the primary key button (looks like a key!).

The primary key would be the better option over a unique index. Setting a primary key on all tables is also something that you should get into the habit of.

DK

 

[jcaptchaos2]

Ok I thought this was working but I guess not, I have set the primary keys to "Employee Number" and "Date" in my table, I only want it to stop me from putting the same employee number in on the same date. I thought it was working but it is blocking the employee number from being entered the next day also so it is not looking at both the employee Number and date together. What am I doing wrong?

 

[jcaptchaos2]

Ok fixed it not sure how but it is working