Update Tables Problem

[jnorris]

I have somewhat of non-standard setup i would presume as this Maintenance DB is being updated from a flatfile DB. Basically, I have 2 tables one called tblETR and the other tblLOG. tblETR primary key is the ETR (Job) number with a one to one relationship to tblLOG.ETR.

WHat I need to do is whenever the user creates or Updates the tblETR, I also have to create a Log entry which can be viewed from the Log table. The user has a requirement that EVERY ETR entry will have the associated memo field in the LOG table updated as well.

Should I use Recordsets to accomplish this and if so how would I go about it?

Thanks in Advance,

Jim Norris

 

[KenReay]

Hi

Are you updating via a table?

If yes, why not make a query joining the two tables, and make this query the recordsource of the form.

Include you memo field from tblLOG on the form as textbox control, with visible = false

Then in the before update / before Insert Event of the form you can put code to update the memo field

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[LittleSmudge]

Nothing in what you have said justifies the table count exceeding ONE.

Why have you chosen to split the data into two tables and then have a ONE-to-ONE relationship between them ?

Putting all of the data into one table fixes your problem - does it not ?


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jnorris]

Ok, guys here's where it gets a little strange. It's not just the memo field in the tblLog. The reason I see for 2 separate tables is that they need to be able to create just a Log Entry when they work on a piece of gear that does not have an ETR number (job number)and be able to keep separate data for lookup later from each table. On the other hand, whenever they open a new job (ETR) or update an existing one they want the memo field in tblETR along with date and a couple other fields to be output to the LOG table as well. This way they can go back and look through Log for say the previous shift and see exactly what was done.

This has been my problem from the start and I think i've been getting a little wrapped around the axle on it ;p

If you need specific breakdown of each table I can give that.

Regards,

Jim

 

[LittleSmudge]

Okay - So here it is.


tblETR
ETR
NextField
OtherField

Etc..

tblLOG
LOGId PrimeKey
ETRRef ForeignKey pointing to tblETR (set to Indexed No Duplicates)
LogField1
LogField2
MemoField
Etc..



Now you can have a form that allows new entries into tblETR and automatically creates a linked entry into tblLog.
All of the fields that you have been thinking of as 'duplicate' need ONLY be stored in the tblLOG. The Form can have full and immediate access to them from there given the ETR number ( via the ETRRef in tblLOG )

You can also have a form that just creates independent records in tblLog - which again all have access to the memo field etc..


'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jnorris]

Thanks for the help but I still have a problem. In tblLOG the ETRRef that you say to use as a foriegn key has to be indexed, this is a problem, there can be more then one entry in the Log on the same job with the same ETRRef? If two diferent techs work on the same piece of equipment 2 days in a row and input entries into the Log.

Below id the breakout of the 2 tables i'm using, I may have to use a different appraoach:

tblETR tblLOG

ETR LOGID
EQUIP SUBJ
Station Station
DATE_Open Date
OPEN_BY Initials
Status ETR_NO
ETR_Ref Manhours
Problem ENTRY
Manhours
Action_Taken

The records that have to be updated when making an entry into tblETR are:

ETR ---> ETR_NO
Station
Manhours
Action_Taken ---> Entry
Problem ---> Subj


Maybe a redesign is in order, although years of data have been transfered from old flat file DB.

Any ideas appreciated

Jim Norris
jim@jnorrisuk.demon.co.uk

 

[LittleSmudge]

In your FIRST post you said
tblETR primary key is the ETR (Job) number with a one to one relationship to tblLOG.ETR.


In your latest post you said
there can be more then one entry in the Log on the same job with the same ETRRef

These two statements seem to be mutually EXCLUSIVE

Have the rules changed?
Have I misinterpreted something?
Did you not mean to say one ( or both ) of them?



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jnorris]

Sorry, I was wrong and herein lies my problem it looks like. There can be more then one entry in the Log on the same job with the same ETRRef. tblETR primary key is the ETR (Job) number (which there can only be ONE ETR number, but there also can be multiple entries for a particular ETR number in tblETR and tblLOG. This should have been obvious to me as you may work on a particular job and make entries for possibly many days until job is done.

So, I guess i need a hard look at relationships first then.


Jim
jim@jnorris.demon.co.uk

 

[LittleSmudge]

Okay Jim. Through conversations like this we ( eventually )get to clarity.


I'd suggest a tblETR that simple has the ETR numbers as Prime Key and maybe Date originally raised etc..

tblETR
ETR Prime Key
RaisedDate

Then a tblETRRecord that is a day's record of activity on that ETR

tblETRRecord
ETRRecordId Prime Key
ETRRef Foreign Key to tblETR
OtherFields
etc..

Then I loose clarity
Does the tblLOG hang off the tblETRRecord ?
Ie. Does a Log entry arise because of one particular day's ETR activity or from the root ETR number ?

( Hang on though. I thought the ETR was the job number and successive activities on that job caused new entries in tblLOG ( not new entries in ETRRecord )


This one will run and run !


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jnorris]

The users want a Log entry generated whenever and entry is made into an ETR record. This is a way for them to look at say a particular shift and see what work was done vice calling up a particular ETR and read. Also not all jobs will have ETR's. For instance something that is done on a daily basis (preventive type maintenance) and replacement of consumables, etc with have only a Log entry and no associated ETR. So can I hang it off the tblETRRecord in this case?


Jim
jim@jnorrisuk.demon.co.uk

 

[LittleSmudge]

The question in my mind is still :-
Do you hang the tblLOG off the tblETR OR hang it off the tblETRRecord.
And I'm not clear why they need to have multiple ETR records for any one ETR number.

As for LOG records, you can have multiple record hung off of whereever we eventually decide to hang them AND you can also have free floating records that don't actually hang off any ETR at all. You just make sure that the tblLOG will allow the Foeirgn Key field to contain a NULL ( Zero Length = Yes, Required = No )

I still need clarification on
( Hang on though. I thought the ETR was the job number and successive activities on that job caused new entries in tblLOG ( not new entries in ETRRecord )



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[jnorris]

Yup, I think it need to hang off tblETR. ETR is a job number and yes, successive activities do cause new entries into tbl Log.

As for the ETR Records, wern't they for Log entries?