Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 7th, 2005, 06:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default two table one form

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




 
Old April 7th, 2005, 06:57 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 7th, 2005, 09:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




 
Old April 7th, 2005, 03:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old April 8th, 2005, 06:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





















 
Old April 11th, 2005, 07:16 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit Table through a Form Coby Access VBA 3 August 17th, 2007 07:10 AM
Form to table in Access 2003 NassauBob Access 2 July 18th, 2007 08:36 AM
Update Table From Form ksbrain Access 2 January 9th, 2007 01:49 PM
Update table from form leeloo Access VBA 1 June 28th, 2004 11:50 AM
How to refresh a table in a form lxu Access 0 March 9th, 2004 05:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.