Normalization and adding multiple records 2

[kc112]

ok..I've normalized! :0)

I have:

Main table: txplandata
PK - key (autonumber)

subtable: dx
PK - pkdx (autonumber)
FK - keytx
field 1: primedx (combo box)
field 2: second dx (combo box)


So, any patient can have multiple prime dx's and multiple second dx. I linked the tables via a relationship.

How do I set this up on a form so that the user can easily select more than 1 dx from the combo box related to the primary key on treatmentplandata?

I tried adding multiple combobox fields on the form, but they all populate with the same (whatever is chosen first).

I thought about switching to data entry, but since its a combo box, I don't see that option.

Any help or direction please!

 

[dhookom]

IMO, you aren't fully normalized until you remove the SecondDX and possibly add a field to store whether the DX is primary, secondary, or whatever.

However, a continuous form is typically used to display and add the DX records. Each record would have a combo box to select the DX. There isn't a simple method for selecting multiple DXs at one time. I expect you could create a multi-select list box on the main form and use code to append the items selected into the related/child table.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Okay...I need more help with normalization.

When I break the dxcode fields up to:

Main table: txplandata
PK - key (autonumber)

subtable: dx
PK - pkdx (autonumber)
FK - keytx
field 1: dx (combo box) *selects a diagnosis code
field 2: rate (combo box) *selects either primary or secondary

What I do not understand is how to have the user select up to 12 records with the same FK (keytx) from the main table from a form?

for example, if I went the 'wrong way', I would just have 12 fields (dx1, dx2, dx3, etc...) and then on my form have dx1, dx2,dx3, etc...

Now, being normalized, I only have dx. If i try to put it multiple times, it generates the same(1) for each box. If I mark as continous, then the user cannot see all 12 dx codes from the form, only the datasheet.

How do people normalize and input more than one record for each ID from a form?

 

[lameid]

Usually the many side of the relationship is a subform on the main form... You just enter multiple records. Beyond that, scrollbars allow you to scoll through the records if they don't fit on the screen at once.

 

[dhookom]

kc112,
It might help to think of this as an Order (txplandata) and OrderDetails (dx). Each of the order details creates a new record for the same order. These are typically displayed (in Access and web pages) with a header section at the top and the individual details in a continuous table under.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Thank you for your responses.

I am looking back into normalization of my tables because I have come a across a very defeating error. I am at the end of creating my database and just finished creating the tables, forms, and reports for the radiological section.

I went to create a test form, entered my appropriate data, clicked print peview and saw a beautiful treatment plan report! Then, clicked on print to send it to the printer and recieved: ERROR: CANNOT OPEN ANY MORE TABLES. ugh...defeat

So, I'm figuring I need to normalize more? Like breakdown my primedx and seconddx fields. But what I dont understand, is how I can have too many tables? Right now, I have about 30 tables (some of which are lookup tables), 1 main form with tabs and around 15 subforms, and a matching report with subreports. I was under the impression that Access 2000 could have 2,048 table links?

Where am I going wrong with this? Do I need to normalize more? Do I need code to ensure my subforms are closing if not in use? Do I need to download a jetpack? (it said I alread had jetpack4)

Please help me, I feel so lost...

 

[dhookom]

Are you using lots of combo boxes or lookup fields in your reports?

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Yes, I am using lots of combo boxes and lookups. I would like to keep them since it eliminates typing the entries by the user and keeps responses uniform (which is important for this program).

I was able to print my report this morning without receiving the 'too many open tables error' by removing the print preview code. I just changed acviewpreview to acviewnormal and it printed. I'm still worried that I just dodged the bullet though and that I am still going to have this problem in the future.

Any ideas on how to clean up the combo boxes/lookups so that they close after use? or ideas to limit my tables?

 

[lameid]

Ary you saying that you have a bunch of comboboxes on your report?

I would instead add the rowsources to the report recordsource query and join base on the FK they are storing and pull the data you want to view.

 

[kc112]

I would instead add the rowsources to the report recordsource query and join base on the FK they are storing and pull the data you want to view. "


Woah! What does that mean? I feel like I'm gonna get yelled at for saying this...but I did not use any queries on my report. My report only prints the primary key that I want via a button from my mainswitchboard form. User selects patient name and viola, single report generated.

Did I go about this the wrong way? I don't have any queries in my whole program.

 

[dhookom]

Are you suggesting all of your report record sources are simply table names?

Your "selects a diagnosis code" table should be joined in to your record source so you don't have to use combo box controls in your report.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Yes, my entire report is made up on tables and subreports(which are comprised from tables).

How do I join via a record source? I know there is a field for record source on the properties tab, but my table name is there.

Also, for the majority of my report I created textboxes to display the information of the combo boxes and set the combo box visible property to NO.

Any help would be appreciated.

 

[kc112]

Ok...been doing some research on what you said. and found this:

Method 2: Add a field from a related table by using the field list
In the Navigation Pane, right-click the report you want to modify, and then click Layout View.
If the Field List task pane is not already displayed, on the Design tab, in the Tools group, click Add Existing Fields.
If Show all tables is displayed at the top of the field list, click it to display fields in related tables and other tables.
Under Fields available in related tables, expand a table, and drag one of its fields to the report.

Access changes the record source to an embedded query that contains the field you added. You can continue adding fields in this manner, or edit the embedded query by using the procedure in Edit an embedded query.

http://office.microsoft.com/en-us/access-help/set-the-record-source-for-a-report-HA101842276.aspx

Is this what you mean? So instead of having lots subreports, I should just add the field directly to the report fom multiple tables? Also, if I cange my layout and do this, will it eliminate some of my open tables?

 

[lameid]

I'm not sure what adding the from the field list does behind the scenes (I'm using an earlier version) but it does seem that modifying the layout view would effectively change the recordsource or create a changed underlying query.

If you go to the below link and look at lesson 5 and "Create a Query That Uses Two or More Tables" that should describe joining tables together. You may need to work through other preceding steps in the lesson to learn about queries.

http://www.baycongroup.com/access2007/index.html

 

[kc112]

Thank you both for your helpful answers. I'm going to go and study that website lameid!

Thanks again!