how can design the access form data insert into two different tables ?

I want to insert data into two tables and use one form
mostly same records have to insert. only one field is different.

table 1
-------
empno
projectno
manhour1
date
..

table 2
-------
empno
projectno
manhour2
date
...

how can make one form and it do insert data two different tables ?
it is possible in access ?

regards.

Mateen

You can do this with a form/subform if the table structure is right. It doesn't look normalized here, unless there is an Emp table and a Project table.

If the structure is like this:

tblEmp
EmpNo = PK

tblProject
ProjectNo = PK

table 1
table1ID = PK
empno = FK
projectno = FK
manhour1
date

table 2
table2ID = PK
empno = FK
projectno = FK
manhour2
date

Then you can create a query on table 1 and table 2, use the empno/projectno in the query to find records or create them, and then base the form on the query.

This is not how I would structure the database, though, I think. Is there a reason that you have two duplicate tables, or apparently duplicate tables? You should be able to store all your manhour data in one table, rather than two duplicate tables.

mmcdonal

Thanks for your response.

Actually company's report requirement is that to calculate the
each employee progress base on each project no and per week and each department.
our payroll base on four weekly. company pay salary four weekly.

hence I make manhours four tables and employee, project and dept table ie.

employee
-------------
empno (pk)
name
...

projectno
-------------
projno (pk)
...

manhour first week
--------------------------
empno (fk)
projno (fk)
mh1
date

manhour second week
--------------------------
empno (fk)
projno (fk)
mh2
date

manhour third week
--------------------------
empno (fk)
projno (fk)
mh3
date

manhour fourth week
--------------------------
empno (fk)
projno (fk)
mh4
date

I want to make one form, like this.

Manhours Form
-------------------------------------
empno...1012
projno.....1026
date......4/6/2005

mh1..... mh2.... mh3.....mh4
10........15.......25.........20
20........35.......25.........10
40........22.......15.........120
....


above data should be save into four table. so that we can make report to calculate
each employee, each project four weekly progress.
ie first week 10 hour work, second week 15 hour work
and third week 25 and fourth week 20 hours work on
one project.

I try to make one table but report requirement not fill.
hence I make four different manhours table.

Please comments.

Regards.

Mateen

Instead of having four manhour tables that are structured the same except for one field, combine them.

empno (fk)
projno (fk)
mhNo
mh
mhdate

mhNo is Man Hours Number (1 through 4 in your case). Also change date to mhdate (or something else). The word "date" is a reserved access function that returns today's date.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for your response.

we have to make report in access. like
this.

Project No. 2016......date:8/4/2005
Dept Highway
------------------------------------
name...Budgeted manhour..mh1,mh2,mh3,mh4...Actual job-to-date....% Utilized
-----------------------------------------------------------------
Bulent.200..............20...15...10..20........65 .
John...200..............20...10...20..10........60
....
--------------------------------------------------------------
.......400...........40...25...30...30.......126 126/400*100=31.5%


I have one more Table Budgeted Manhour. management
assign Budgeted hours to each employee to each project.

Budgeted Manhours table
----------------------
empno
projno
BH
date

If I make one table instead of four manhours then above report can be generate in Access Report ?

Please comments how ?

regards.

Mateen

Absolutely.. a cross-tab query will work nicely with a single table to produce the report specified.. it can be done other ways too, but a cross-tab seems built for it, frankly.

They work like pivot tables... the empno, projno would be your Row headers, the mhNo will be your column header and the mh field would be your value (sum, count, avg, min/max, whatever you need)... this way, as a bonus, when you add new projects you won't need to re-code the whole thing, too...

Though, dependant on how you want to display this data (exported query, form, report etc) you may need to do some re-plumbing when new projects are added or removed, even if it is merely adjusting your page widths.