A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple EmployeeID fields in a table



 
 
Thread Tools Display Modes
  #1  
Old February 22nd, 2006, 01:51 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

I believe my tblECR Needs to be broken apart to achieve 1 EmployeeID field
per table, but I'm not too sure on the best approach for this. This is an
engineering ECR tracking database where each record shows multiple tasks
performed by multiple employees. Can someone point me toward north on this?
Thanks!

Tables:
tblCustomers
CustomerID
CustomerName

tblECR
ECRNumber
PartNumber
CustomerID
DateInitiated
|-------------OriginatedBy
| ChangeSummary
| JobNumber
|-------------LoggedBy
| ImportanceID
|-------------ReviewedBy
| DateReviewed
| DateReq
| VantageChecked
| Attachments
| ReqDwg
|-------------DwgRevBy
| DwgRevDate
| ReqMaterials
|-------------MaterialsRevBy
| MaterialsRevDate
| ReqCutlist
|-------------CutlistRevBy
| CutlistRevDate
| ReqProgram
|-------------ProgramRevBy
| ProgramRevDate
| EngNotes
|
| tblEmployees
|-------------EmployeeID
FirstName
LastName

tblImportance
ImportanceID
Importance

tblParts
PartNumber
PartDesc

  #2  
Old February 22nd, 2006, 03:44 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

I would remove the dates and employees from tblECR. Assuming ECRNumber is
the primary key, create a related table like:

tblECRTasks
===================
ecrtskID autonumber primarykey
ECRNumber links to tblECR.ECRNumber
EmployeeID links to tblEmployees.EmployeeID
TaskID links to a new table of tasks
TaskCompleteDate

--
Duane Hookom
MS Access MVP
--

"Dave Hoder" wrote in message
...
I believe my tblECR Needs to be broken apart to achieve 1 EmployeeID field
per table, but I'm not too sure on the best approach for this. This is an
engineering ECR tracking database where each record shows multiple tasks
performed by multiple employees. Can someone point me toward north on
this?
Thanks!

Tables:
tblCustomers
CustomerID
CustomerName

tblECR
ECRNumber
PartNumber
CustomerID
DateInitiated
|-------------OriginatedBy
| ChangeSummary
| JobNumber
|-------------LoggedBy
| ImportanceID
|-------------ReviewedBy
| DateReviewed
| DateReq
| VantageChecked
| Attachments
| ReqDwg
|-------------DwgRevBy
| DwgRevDate
| ReqMaterials
|-------------MaterialsRevBy
| MaterialsRevDate
| ReqCutlist
|-------------CutlistRevBy
| CutlistRevDate
| ReqProgram
|-------------ProgramRevBy
| ProgramRevDate
| EngNotes
|
| tblEmployees
|-------------EmployeeID
FirstName
LastName

tblImportance
ImportanceID
Importance

tblParts
PartNumber
PartDesc



  #3  
Old February 22nd, 2006, 07:19 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

Thanks Duane!
That's the kick I needed. This started as a one table example that I whipped
up to show the powers that be what was possible but they insisted on using
Excel. I kept up with the Excel records for a while & have a couple hundred
records that I'd like to keep. Any way you can see to re-shuffle the data or
do I need to start over? I don't think importing is an option since the
formatting is a mess (each line of a description on a different row, etc. THE
HORROR!). They now have 450 tabs on 3 Excel files & they're all a disaster.


  #4  
Old February 22nd, 2006, 09:13 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

You might be able to salvage some stuff from Excel. It's hard to say. I
sometimes import and other times copy and paste.

With 450 tabs in 3 files, you have a lot of work ahead of you.

--
Duane Hookom
MS Access MVP
--

"Dave Hoder" wrote in message
...
Thanks Duane!
That's the kick I needed. This started as a one table example that I
whipped
up to show the powers that be what was possible but they insisted on using
Excel. I kept up with the Excel records for a while & have a couple
hundred
records that I'd like to keep. Any way you can see to re-shuffle the data
or
do I need to start over? I don't think importing is an option since the
formatting is a mess (each line of a description on a different row, etc.
THE
HORROR!). They now have 450 tabs on 3 Excel files & they're all a
disaster.




  #5  
Old February 22nd, 2006, 09:38 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

Also, I'm guessing now that I'll have multiple task records for each ECR I'll
need a subform to display what's been done & allow the user to check off that
a task has been completed?

"Duane Hookom" wrote:

I would remove the dates and employees from tblECR. Assuming ECRNumber is
the primary key, create a related table like:

tblECRTasks
===================
ecrtskID autonumber primarykey
ECRNumber links to tblECR.ECRNumber
EmployeeID links to tblEmployees.EmployeeID
TaskID links to a new table of tasks
TaskCompleteDate


  #6  
Old February 22nd, 2006, 10:09 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

Yep. You should have a subform that allows any number of tasks with the
associated fields. This is very common.

--
Duane Hookom
MS Access MVP
--

"Dave Hoder" wrote in message
...
Also, I'm guessing now that I'll have multiple task records for each ECR
I'll
need a subform to display what's been done & allow the user to check off
that
a task has been completed?

"Duane Hookom" wrote:

I would remove the dates and employees from tblECR. Assuming ECRNumber is
the primary key, create a related table like:

tblECRTasks
===================
ecrtskID autonumber primarykey
ECRNumber links to tblECR.ECRNumber
EmployeeID links to tblEmployees.EmployeeID
TaskID links to a new table of tasks
TaskCompleteDate




  #7  
Old February 24th, 2006, 03:07 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default Multiple EmployeeID fields in a table

Thanks Duane,
If you're still watching this thread I have one more question. I set up my
database as you suggested & have a subform showing completed tasks for the
ECR. The subform is locked & I have a button to bring up another form that
automatically enters the ECR number in the proper field & the user must enter
their name, task completed & date. when they close the form the subform at
the bottom of the main ECR form shows the list of completed tasks. However
one of the tasks is to actually enter the ECR itself. It seems to make more
sense visually to leave the "OriginatedBy" & "DateInitiated" fields in the
main table so the user can just tab through the fields & save the record for
action at a later date. If I do this & link the
EmployeeID to 2 tables, I get no records in my query. Can you give me some
advice on how to approach this?
Thanks for your help!

"Duane Hookom" wrote:

Yep. You should have a subform that allows any number of tasks with the
associated fields. This is very common.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query is not updatable - Doug Johnson via AccessMonster.com Running & Setting Up Queries 3 January 21st, 2006 01:36 AM
Seeking some expert advice. HD87glide Using Forms 14 March 23rd, 2005 11:11 PM
Automatic filling of fields in table two from table one Jim Kelly Database Design 1 September 27th, 2004 10:16 PM
Complicated Databse w/many relationships Søren Database Design 7 July 13th, 2004 05:41 AM
COMPARE THE TWO TABLES Stefanie General Discussion 0 June 4th, 2004 04:36 PM


All times are GMT +1. The time now is 02:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.