converting minutes to hours in access 2

[lychee]

i imported a text file into access that has minutes in a column. i would like to have the minutes in the column be converted to hours. is there a way to do this in the data design view? i also notice that the explanations in these forums are aimed at programmers (and there appear to be a lot of knowledgeable people here), but unfortunately, i am not one and would appreciate a general user explanation. thanks.

 

[dhookom]

To get hours from minutes, divide by 60. I would leave the values stored as minutes and when ever you need to display hours, just divide by 60.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lychee]

duane, thank you for answering, i guess i didn't ask the question right - but where do i divide by 60 in the database? in design? or is that even possible?

 

[BNPMike]

Store the bare data in your table but view it via a Query. When you construct the query create two new fields which generate the hour and minute parts of the time field. Then hide the time field itself.

 

[lychee]

thanks bnpmike, i knew i could do it in a query, but i didn't know if i could do it in the table, i assume not?

 

[dhookom]

lychee,
There should be no reason to do it in the table and it can't to my knowledge be done in the table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

Treat tables like the insides of your car's engine compartment. You have to have accellerators, ignition advance/retard and gears but you get them wrapped so all you see is pedals and an automatic gear programme selector.

Queries present a user-friendly view of the underlying data.

 

[lychee]

ok, got it, thanks guys. the only reason i was asking was because i will have to download this text file perodically and it has minutes which i will have to convert to hours, but i was hoping that in the import process, there was a formula i could create which will do the coversion for me, then if not in the table. i got my answer, thanks again
i do have another question - when i am in the query-
e.g D means 0700 - 1530 for department A, but D also means 0800 - 1630 for department B, and i want just the codes for department B, but there also some codes that belong to Department A which have no similar codes in department B, that i also want along with department B codes. in other words, department B will use some of the same codes that department A uses because of the same start and end times, but there are some codes they created that have different start and end times.

 

[BNPMike]

The answer to this depends on how many departments. If there many departments then I think you are going to have to have a table like this

Dept Code Start End
A D 0700 1530
B D 0800 1630
A N 2200 0600
B N 2200 0600

You then join on Dept and Code to get the correct times.

 

[lychee]

bnpmike, your example is exactly what i was talking about, but what if they are on the same table? how do i differentiate between a code that is identical and therefore not to be repeated like in the last 2 of your example, as opposed to the top 2, where i also want the dept A code along with dept B code?

 

[BNPMike]

If you do it like I've just showed you then you can just join and get the right times. You don't have to worry about whether some codes are the same as others.

 

[lychee]

bnpmike, i am sorry, i guess either i am not understanding what you are telling me or i did not get my question across. so let me try again. if i have both dept A and B codes in the same table, but i don't want to see dept A codes - if they have the identical start times as dept B. i do want to see Dept A codes however, if they are different from Dept B, as well as all the dept B codes.