time & billing database

[Guest]

Please help, how can I show this in a form of an access database?

AutoID ProjectName 1/1/05 1/2/05 1/3/05 ...... 1/31/05
1 Proj01 4 Hrs 2 0 .... etc
2 Proj02 0 Hrs 2 0 .... etc
3 Proj03 4 Hrs 3 0 .... etc
4 Proj04 0 Hrs 1 8 .... etc
etc.

Thanks.

 

[Joseph Meehan]

Please help, how can I show this in a form of an access database?

AutoID ProjectName 1/1/05 1/2/05 1/3/05 ...... 1/31/05
1 Proj01 4 Hrs 2 0 .... etc
2 Proj02 0 Hrs 2 0 .... etc
3 Proj03 4 Hrs 3 0 .... etc
4 Proj04 0 Hrs 1 8 .... etc
etc.

Thanks.

First step is saving the information in an Access Database.

It appears you have fields for each date and a field for each project.
Normally it is better to have one table for projects listing the project,
maybe a description and information unique to that project. Next you would
have a second table where all the hours go linked to the first table. A
record in the second table would have things like the date, number of hours,
maybe who billed the hours etc. Each entry would be a separate record.

Is this how your data is recorded?

 

[Guest]

Hi Fehn,

Would you be happy with the results of a crosstab query, without attempting
to display the results in a form? If so, check out this link:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


While you can display the results of a crosstab query in a form, you will
need to use a bit of complicated VBA code if the dates are not static. At
this point, you'd probably be better off to use a PivotTable if you have
Access 2002 or later.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Please help, how can I show this in a form of an access database?

AutoID ProjectName 1/1/05 1/2/05 1/3/05 ...... 1/31/05
1 Proj01 4 Hrs 2 0 .... etc
2 Proj02 0 Hrs 2 0 .... etc
3 Proj03 4 Hrs 3 0 .... etc
4 Proj04 0 Hrs 1 8 .... etc
etc.

Thanks.

 

[Guest]

Yes Joseph, this is how the data is recorded in excel.

The projectname is actually link to another table that have all the projects.

Every month we are creating an excel spread for the time sheet and the only
change in the spreadsheet is the dates.

 

[Guest]

Thanks Tom.

I will wheck that link.

Just a brief history on why im doing this.

Before I have a databse that shows the following:

Date ProjectName NumHrs
1/1/05 Project01 4
1/1/05 Project02 4
1/2/05 Project01 4
1/2/05 Project01 2
1/2/05 Project01 2
etc.

My co workers doesn't like that form.

They want a to have all the days of the month showing up on the form and all
they need to do is put the project name and the corresponding hours that they
worked on days that they worked.

So I made that in excel. But I still want to use access.

Thanks.

 

[Joseph Meehan]

Yes Joseph, this is how the data is recorded in excel.

The projectname is actually link to another table that have all the
projects.

Every month we are creating an excel spread for the time sheet and
the only change in the spreadsheet is the dates.

So the data started out in Excel. OK I see the problem. I would like
to see it imported into Access in a normalized table design.

Will you be taking this project into Access or will it be run on both
Access and Excel?

It is is being done over in Access, I suggest you change the way the
data is stored and use related normalized tables before you worry about how
it will be displayed. I believe that once normalized properly you will find
it far easier to produce the final product and have it work like the users
want.

 

[Guest]

Once I got this working in access, then they will use only access.

Just a brief history on why im doing this.

Before I have a databse that shows the following:

Date ProjectName NumHrs
1/1/05 Project01 4
1/1/05 Project02 4
1/2/05 Project01 4
1/2/05 Project01 2
1/2/05 Project01 2
etc.

My co workers doesn't like that form.

They want a to have all the days of the month showing up on the form and all
they need to do is put the project name and the corresponding hours that they
worked on days that they worked

Thanks.

 

[Guest]

Hi Fehn,

In that case, a crosstab query will not be helpful because it produces a
read only recordset.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thanks Tom.

I will wheck that link.

Just a brief history on why im doing this.

Before I have a databse that shows the following:

Date ProjectName NumHrs
1/1/05 Project01 4
1/1/05 Project02 4
1/2/05 Project01 4
1/2/05 Project01 2
1/2/05 Project01 2
etc.

My co workers doesn't like that form.

They want a to have all the days of the month showing up on the form and all
they need to do is put the project name and the corresponding hours that they
worked on days that they worked.

So I made that in excel. But I still want to use access.

Thanks.
__________________________________________

:

Hi Fehn,

Would you be happy with the results of a crosstab query, without attempting
to display the results in a form? If so, check out this link:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


While you can display the results of a crosstab query in a form, you will
need to use a bit of complicated VBA code if the dates are not static. At
this point, you'd probably be better off to use a PivotTable if you have
Access 2002 or later.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Please help, how can I show this in a form of an access database?

AutoID ProjectName 1/1/05 1/2/05 1/3/05 ...... 1/31/05
1 Proj01 4 Hrs 2 0 .... etc
2 Proj02 0 Hrs 2 0 .... etc
3 Proj03 4 Hrs 3 0 .... etc
4 Proj04 0 Hrs 1 8 .... etc
etc.

Thanks.

 

[Guest]

Hi Tom,

Do you have any suggestions on what i need to do?

Thanks

 

[Guest]

Hi Fehn,

Yes. Tell your users "no can do". This isn't Excel, so they shouldn't expect
things to look and feel like Excel.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi Tom,

Do you have any suggestions on what i need to do?

Thanks

 

[Guest]

Hahaha! Thanks Tom. That brighten up my day!

Although, I can not say that to the users.

Thanks again.

 

[Larry Linson]

This may be one of those rare times that an unbound form is the best
solution. If you store the data properly, but create an unbound form in the
format the users desire, then use code behind that form to write the data to
the normalized database, that could work.

Alternatively, you could, in fact, keep the data in the form you now have,
but before you make other use of it (e.g., reports, analysis, invoices),
split it out to a properly normalized form so that queries are simple.

Larry Linson
Microsoft Access MVP

 

[Guest]

Do you have any samples that I can follow?

This may be one of those rare times that an unbound form is the best
solution. If you store the data properly, but create an unbound form in the
format the users desire, then use code behind that form to write the data to
the normalized database, that could work.

Alternatively, you could, in fact, keep the data in the form you now have,
but before you make other use of it (e.g., reports, analysis, invoices),
split it out to a properly normalized form so that queries are simple.

Larry Linson
Microsoft Access MVP

 

[Joseph Meehan]

Once I got this working in access, then they will use only access.

Just a brief history on why im doing this.

Before I have a databse that shows the following:

Date ProjectName NumHrs
1/1/05 Project01 4
1/1/05 Project02 4
1/2/05 Project01 4
1/2/05 Project01 2
1/2/05 Project01 2
etc.

My co workers doesn't like that form.

They want a to have all the days of the month showing up on the form
and all they need to do is put the project name and the corresponding
hours that they worked on days that they worked

Thanks.

It certainly is possible to do. It would take some VBA programming, at
least I can't think of a way of doing it otherwise. I am so slow at VBA I
fear it might take me all day to work it out. Maybe someone else in the
newsgroup can offer a reference or will have another idea. I really think
in the long run it would be worth it. If it were my project, I would work
at it.

I am thinking of building a form and creating an array. The array would
have 31 x X fields where X would be the max number of possible projects for
one day (or really one entry session) Then when they are done, using that
information to actually enter the data into the table.

 

[Guest]

i'm not that familiar with vba.

Do you have an example in access?

thanks joseph

 

[Joseph Meehan]

i'm not that familiar with vba.

Do you have an example in access?

thanks joseph

It certainly is possible to do. It would take some VBA
programming, at least I can't think of a way of doing it otherwise.
I am so slow at VBA I fear it might take me all day to work it out.
Maybe someone else in the newsgroup can offer a reference or will
have another idea. I really think in the long run it would be worth
it. If it were my project, I would work at it.

I am thinking of building a form and creating an array. The
array would have 31 x X fields where X would be the max number of
possible projects for one day (or really one entry session) Then
when they are done, using that information to actually enter the
data into the table.

I am sorry for not responding sooner. I have not had time to work out
the answer to your question. Hopefully someone will notice this response
and offer some additional help. If not, try reposting your question.