relationship for creating a data entry form

[BittenApple]

Hello team,


I am in the process of creating a form for data entry.
I start with the design of tables of their relation to each other
1-Hospital table, fields: AutoNumber (as Pk), Name of hospitals (a combo box for this table)
2-Department table AutoNumber (as PK), Name of departments (a combo box for this table)
3-hosDepTable: autoNumberFromHospitableTable, autoNumberfromDepartmentTable (one hos may have many depmartments and one department can be in many hospitals) can this approach be correct?
4-Transaction table, fields: AutoNumber from Hospital table, AutoNumber from department table, plan, itemG, Even, project (a data entry form for this table), the
5-Rate table: AutoNumber (as pk), fields: year 2013, year 2014, year 2015, cc, bb, kk ( I don't know how to split this table, crate, brate, krate are flat for year 2013 and 2014 and only those are changed for 2016). How to split this table?


Regards,
BittenApple

[HansV]

How can one department belong to multiple hospitals?

[BittenApple]

One department doesn't belong to multiple hospitals, however, each hospital has the same departments such as admin, admission, billing and so on.
Regards,
BittenApple

[HansV]

Are the rates the same for all hospitals and all departments?

[BittenApple]

Rates are not same for all hospitals; each hospital has a different rate for fields below for year 2013,2014,2015.
BB CC DD
-but rates for 2013 and 2014 are not going to change, but it changes on 2015. We have to update this table with the amount that we have on weekly basis.
Regards,
BittenApple

[HansV]

The hospital ID should not be an AutoNumber field in the rates table. The design of the rates table could look like this:

S1454.png

And in datasheet view it would look like this:

S1456.png

[HansV]

In the hospital department table, neither the HospitalID field nor the DepartmentID field should be AutoNumber fields. Both should be number fields that link to the HospitalID field in the hospital table and to the DepartmentID field in the department table, respectively.
The primary key in this table should be an AutoNumber field HospitalDepartmentID or similar. This field doesn't link to either the hospital table or the department table.

In the transactions table, the primary key can be an AutoNumber field TransactionID. There is no need for a HospitalID or DepartmentID field in this table. You do need a number (not AutoNumber) field HospitalDepartmentID that will be the foreign key linking to the HospitalDepartmentID in the hospital department table.
The transactions table does not link directly to the hospital table, nor to the department table.

[BittenApple]

Hans,
Do we really need a link table for many to many relationship, I mean hospital_department table?
Regards,
BittenApple

[HansV]

Yes.

[BittenApple]

Hello Hans,
I created the form,
-I changed table hospital into a combo box and it went well; Combox selection gets entered into transactional table. I added HospitalName to transactional table since I wanted to store the value of combo box selection into transactional table.
-I changed table department into a combo box and it went well; Combox selection gets entered into transactional table. I added DepartmentlName to transactional table since I wanted to store the value of combo box selection into transactional table.

The composite key which is made of HospitalID+DepartmentID and it is a fk for tranactionaltable is not being generated into transactionaltable. Should it be generated automatically? Should I do something about it? Nor this composite key is containing a value. I didn't link HospitalID to transactional table nor I did departmentId to transactional table.

I linked RateID of rate table to RateID of transactional table. That Rate ID which is a FK in transactional is not getting generated in transactional table.

Can you please help me with this?

I appreciate your help,
Regards,
BittenApple

[HansV]

I'm not sure I understand what you have done. Could you create a copy of your database without sensitive information, zip it and attach the zip file to a reply?

[BittenApple]

Hans,
I will probably have to do it.
Let me put my questions in other way. how can we get the the value of RateID as FK (number) in transactional table?
1-RateID is generated as autoNumber in RateTable, but we have to use this value as Fk in trans table.
2-The goal is when an end user selects a hospital name, the fields in RateTable populates the textboxes on the form. One text box for each field.
Regards,
BittenApple

[HansV]

This kind of thing is usually done through forms and subforms.
You'll find a simple example at https://www.dropbox.com/s/aohysaxp646ws ... y.mdb?dl=1" onclick="window.open(this.href);return false;

[BittenApple]

I can try form and sub-forms, but I thought there might be an easier way. for example: selecting an entry form a combo box and then and showing other fields of the table.
I tried a VBA code:
me.text.Value=me.cbo.column(1), I am not sure about this syntax.
I can stop using the RateID in TransactionTable.
Then use the top code.
Regards,
BittenApple

[HansV]

You don't have to use code, you can set the Control Source of a text box to

=[mycombo].[Column](1)

where mycombo is the name of a combo box. The combo box should have multiple columns, of course.

[BittenApple]

Hans,
The db is attached.
I need to be able to build a form to shows two combo boxes for depName and hosName (I made the combo boxes, but not any more, I can not select anything from combo boxes) and two fields myfield and yourfield; in addition to that one should be able to select a hosName in combo box on the form, can get the values of argu table in textboxes as well.

I think what you put on the top post can be very helpful.


Regard,
BittenApple

[HansV]

The track table should NOT contain fields depName and hosName. They would be superfluous, since depName and hosName depend on hos_dep_Id.

I assume that argu is the rates table. If you're going to link argu to track by metID, there shouldn't be a hosID field in the argu table. But since you haven't explained yet how the argu table is going to be used, I don't know if this table structure and relationship is correct.

In the attached version I have created a form frm_hosdep with combo boxes for hospital and department, and a subform sbf_track in which you can enter myfield and herfield. The subform will not be shown in a new record until you select a hospital or department.

Since I don't understand the role of the argu table, I haven't added it.

database.zip

[BittenApple]

Hans,
I assume that argu is the rates table. Yes, it is and it comes with data already loaded into it.
I need when a hosName is selected in combo box, the values from argu or rate table populate some text boxes on the form, this is its role.
I appreciate your response.
BittenApple

[HansV]

The argu table has a Year field, but none of the other tables has a date or year, so I wouldn't know which record to display.

[BittenApple]

Ok for each hos there is three rows of data for 2013 2014 and 2015. When an end user selects an entry from his combos, that selection should pull data for that his name from table rate(the same exact design that you contained in this post), for years 2013 2014 and 2015 and populate the text boxes on the form. Regards bittenappple