Multiple records with pre-population

[Laura Sunny]

Hello Experts,

My name is Laura, I'm fairly new to access; having totally overlooked it as an application till now.
I think I have the jist of the most basic elements of Access but I'm struggling to understand how to structure a table and/ or make a form for the following requirements:

Every Friday, we record (currently in excel) which site a subcontractor has worked at on different days, then add up the days they were in and pay them accordingly.
I'd like to be able to have a form open up pre-populated with the week commencing date and then in a sort of tabular: Monday, Tuesday, Wednesday.... as columns, with rows pre-populated with subcontractors names. Then drop downs under each day with Sites, so that the foreman can easily complete this weeks diary.
It doesn't have to be exactly this way, it needs to be in as few clicks as possible or I'll end up with them doing the usual of drawing a grid on a piece of paper and sending me a picture of the "diary" .

I have a Site table, a Subcontractor table, a Diary table. I have set the Diary table up to record one day , per subcontractor as a single record as I thought this was the most flat way to do it. I could combine days of the week or subcontractors into each row but I think that will cause problems down the road.

In case more context is helpful, this is intended to calculate wages but also days spent on different sites for invoicing etc, we are a small building company.

I hope that makes sense and thank you very much for any help.

Laura

 

[June7]

Can batch create records with an INSERT SELECT action SQL. So if you want a record for each contractor for a specific date, like:

CurrentDb.Execute "INSERT INTO Diary(ContractorID, CommenceDate) SELECT ContractorID, #" & Me.tbxDate & "# FROM Subcontractor"

Then open form filtered to that date to enter other data in records.

 

[Uncle Gizmo]

Could you show a labelled picture of the form layout you require?

 

[theDBguy]

Hi. Welcome to AWF!

The layout you've described sounds like you may still be wearing your Excel (flat file structure) hat, rather than your Access (relational database) hat.

 

[Pat Hartman]

Welcome aboard. If you give it a chance, you'll find Access much better for building this type of application than Excel but you need to take your Excel hat off to come to grips with how to design properly normalized tables. The biggest advantage to a tool like Access is that the data and the presentation and business rules layers are all separate. That allows you to optimize each of them.

Although you may want reports organized with days as columns, there isn't any reason or advantage to making the data entry form a grid. Plus, in your case, it interferes with the processing since your data entry is three-dimensional, not two. Using days for columns and rows for people, the intersection or as they are called in Excel "cells" would hold the time. This leaves no place for the site.

How does the data look as you receive it? Data entry is easiest when it follows the printed form the person is entering data from. If the foreman is doing the data entry, then he is using some type of hardcopy. When individuals enter their own time, they do it each day and probably from memory unless they have multiple projects going on. In that case, they should be entering time as the move from task to task.

Start by showing us a picture of the input the foreman is using. Also, can a person work at multiple sites on a given day? Maybe, or sometimes is a yes to that question. Or perhaps you can see it being needed in the future also is a yes to the question.

 

[Laura Sunny]

Welcome aboard. If you give it a chance, you'll find Access much better for building this type of application than Excel but you need to take your Excel hat off to come to grips with how to design properly normalized tables. The biggest advantage to a tool like Access is that the data and the presentation and business rules layers are all separate. That allows you to optimize each of them.

Although you may want reports organized with days as columns, there isn't any reason or advantage to making the data entry form a grid. Plus, in your case, it interferes with the processing since your data entry is three-dimensional, not two. Using days for columns and rows for people, the intersection or as they are called in Excel "cells" would hold the time. This leaves no place for the site.

How does the data look as you receive it? Data entry is easiest when it follows the printed form the person is entering data from. If the foreman is doing the data entry, then he is using some type of hardcopy. When individuals enter their own time, they do it each day and probably from memory unless they have multiple projects going on. In that case, they should be entering time as the move from task to task.

Start by showing us a picture of the input the foreman is using. Also, can a person work at multiple sites on a given day? Maybe, or sometimes is a yes to that question. Or perhaps you can see it being needed in the future also is a yes to the question.

Hi Pat, Thanks for replying. You are right that I am having to shift from excel thinking. The fact it is 3 dimensional is what is causing me the headache for sure. The way the foremen enter their data currently is from memory at the end of the week. They call me up on the phone and tell me John was at Site 1 on Monday and Wednesday etc. Or they draw out a calendar and hand write it. I have suggested a daily record but they are not keen and like to 'do the wages' on Friday. I didn't set up the original data recording in excel, its just a diary template, there's no calculations in it. It's all just typed in.
It is true that a person can move sites in a day usually we just record this as AM/ PM but again it's just written.
What I'd like to avoid is a form per subcontractor per day, as they wont use it.

Thank you

 

[Laura Sunny]

Can batch create records with an INSERT SELECT action SQL. So if you want a record for each contractor for a specific date, like:

CurrentDb.Execute "INSERT INTO Diary(ContractorID, CommenceDate) SELECT ContractorID, #" & Me.tbxDate & "# FROM Subcontractor"

Then open form filtered to that date to enter other data in records.

Thanks for your reply June7. Sadly this has gone over my head. But I appreciate your response.

 

[Laura Sunny]

Could you show a labelled picture of the form layout you require?

Hey Uncle Gizmo thanks for your reply. I am actually fairly flexible on the layout, its that I need to record 3 dimensional data as Pat has picked up on above. The foremen are used to a grid calendar format. My first thought then was to try and recreate this look but I'm open to any way to record multiple people's locations on different days in one form that's quick.

Thank you

 

[Pat Hartman]

The reason I asked for a definitive answer regarding site was because of June's suggestion. If a person always only worked one site, then you could default site to the person table and use June's suggestion. Otherwise, you have to enter the data row by row, site by site because you don't know ahead of time which site or how many sites were worked each day.

Now another question. Does each site have a foreman so he will be only entering data for his site? If that is true, then you would organize the data entry by site. You could use unbound fields on the main form for site and week start data and using code, populate the values in the subform after the foreman chose a contractor and a weekday and hours. The site would be associated with the foreman so he can log himself in or he can pick the site. The week start date can always be assumed to be the previous Saturday since you are doing this on Friday but the date needs to be unlocked so it can be overridden.

At a minimum, you need to enter day, hours, and contractor. You may also have to enter site. Day, Contractor, and Site are combos. Hours has to be flexible but you can have shortcuts for entering 8 or 4 to represent full or half day.

 

[Laura Sunny]

The reason I asked for a definitive answer regarding site was because of June's suggestion. If a person always only worked one site, then you could default site to the person table and use June's suggestion. Otherwise, you have to enter the data row by row, site by site because you don't know ahead of time which site or how many sites were worked each day.

Now another question. Does each site have a foreman so he will be only entering data for his site? If that is true, then you would organize the data entry by site. You could use unbound fields on the main form for site and week start data and using code, populate the values in the subform after the foreman chose a contractor and a weekday and hours. The site would be associated with the foreman so he can log himself in or he can pick the site. The week start date can always be assumed to be the previous Saturday since you are doing this on Friday but the date needs to be unlocked so it can be overridden.

At a minimum, you need to enter day, hours, and contractor. You may also have to enter site. Day, Contractor, and Site are combos. Hours has to be flexible but you can have shortcuts for entering 8 or 4 to represent full or half day.

It is quite flexible. There are usually no more than 4 sites, with only 2 foremen, who move around. The foremen will tell me where anybody they know has been for the week. It's a very organised business.

The numbers aren't huge here, maybe max 14 subcontractors each week but could be as little as 6.
I think by site wont work. If it must have a single start point it is probably by Subcontractor as that's how they usually impart the information to me. They might call me up on Friday and say "Tom was on site 1, Monday, Tuesday, Wednesday, but you'll have to ask Foreman 2 where Tom was on Thursday and Friday.

For this Reason I was hoping for a form that allowed them to enter what they know but it get stored as individual records on the Diary table.
It is probably I need to watch many more YouTube videos to get my head around it and I'm asking it all the wrong way round. I am grateful for your help.

 

[Pat Hartman]

I would get rid of the grid. It is too awkward when you have more than two dimensions. You can do the data entry with a couple of key strokes as long as you use a default for hours.

a couple of key strokes to pick contractor
a couple to pick site
1 or 2 to pick day or only 1 if you make your combo text field - 1-Sun, 2-Mon, 3-Tue, etc. Your code will convert this to the actual date based on the default placed in the unbound field when the form opens.
You can also take advantage of cntl-quote to copy the value in the field on the line above so if you are doing multiple entries for a contractor, you can use that shortcut OR you can make your own to copy the whole record. You can have it increment the day by 1 as it copies the contractor and site.

Once you get the hang of it, if the foreman is orderly in his dictation, the data capture will be very quick. After you collect the hours, you should probably send an email or print out a report for each contractor recapping the info so he can check for mistakes before it goes to payroll.

 

[Laura Sunny]

I would get rid of the grid. It is too awkward when you have more than two dimensions. You can do the data entry with a couple of key strokes as long as you use a default for hours.

a couple of key strokes to pick contractor
a couple to pick site
1 or 2 to pick day or only 1 if you make your combo text field - 1-Sun, 2-Mon, 3-Tue, etc. Your code will convert this to the actual date based on the default placed in the unbound field when the form opens.
You can also take advantage of cntl-quote to copy the value in the field on the line above so if you are doing multiple entries for a contractor, you can use that shortcut OR you can make your own to copy the whole record. You can have it increment the day by 1 as it copies the contractor and site.

Once you get the hang of it, if the foreman is orderly in his dictation, the data capture will be very quick. After you collect the hours, you should probably send an email or print out a report for each contractor recapping the info so he can check for mistakes before it goes to payroll.

Ok thanks Pat. I appreciate your advice. Thanks so much.