Merging two tables

[cheiboy]

How difficult is it to merge two tables that are linked by a control number without mixing them up. I have two tables that make up a form and a sub form linked by a control number. I want to make one table out of them to eliminate duplicating "concern" information in the subform. This explanation may be too vague.

 

[lespaul]

You usually use sub-form when trying to represent a one to many relationship (ie - the customer information in the main form and all the orders in the sub form).

So when you say

I want to make one table out of them to eliminate duplicating "concern" information in the subform

There shouldn't be any duplication in a normalized database. Is this database normalized?

 

[cheiboy]

This is similar to a customer database, except that it is patient based. But since each new 'complaint' is different that the one before, and not very many are the same, It would require a new 'concern number'. Normally there is no duplication, but lately the RN's have been adding a second line to put in additional information, but when it comes time to do the report, it's counting the second line twice as a separate case, when it's truly not. The only other way I can think of doing this is running a query of all the fields, exporting to excel, working on it there and bringing it back into Access.

 

[fkegley]

I agree with lespaul. The data as you describe should be in two tables. I would, however, convert the description of the "concern" into a type Meme field and give the nurses a big, fat text box in which to type.

Frank kegley
fkegley@hotmail.com

 

[cheiboy]

There is a field for 'COMMENTS', whre they do just that, in the CONCERNS table. Is there a way I can limit them from entering another case/record for the same patient.

 

[OhioSteve]

Post all the tables, their fields, and their keys. That will make it much easier for us to provide assistance.

My gut feeling is that your DB is not normalized.

 

[cheiboy]

Sure, here they are:

The fields from the CONCERNS table:
NewCONTROL_NO
CONTROL_NO
REVIEWER
REFERAL_DT
RESP_DEPT
RESP_RECDT
PRIMDOC_REVIEWER
CONCRN_CD
CONCRN_CAT
REFERL_CD
AGENCY_NAM
AGENCY_ID
SUB_CAT
RESULT_CD
ACTION_CD
REPSRC_CD
CLOSING_DT
OCCURRENCE_CD
RESPONSIBILITY_CD
REFFEREDTO_CD
COMMENTS

and the fields from the MEMBER table:

NewCONTROL_NO
CONTROL_NO
RECVD_DT
RECVD_DEPT
LASTNAME
FIRSTNAME
MEMBR_ID
MEMBR_PID
DOB
PLAN_ID
PROV_NAME
PROV_ID
OCCURNC_DT

The two table above are the ones that have all the data, all the rest are used only as drop down menus.