time & billing database

F

Fehn

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.
 
T

Tom Wickerath

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.
 
F

Fehn

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.
 
F

Fehn

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.
 
F

Fehn

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.
 
T

Tom Wickerath

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.
 
F

Fehn

Hahaha! Thanks Tom. That brighten up my day!

Although, I can not say that to the users.

Thanks again.
 
L

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
 
F

Fehn

Do you have any samples that I can follow?

Larry Linson said:
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
Click to expand...
 
Top