relationships

[killerleaf]

I have a really hard time with this....

I have a table, labeled Main Patient Table.

I have a query, that dumps the results into a table called Patients Currently on Service.

If I make changes to a record while in the Patients Currently on Service, it does not carry to Main Patient Table.

How do I get it to do that? I know I am probably missing something, like they have to be related, (currently, they are not) but I have tried and it won't work. What have I done wrong?

 

[drjekyll325]

An update to one Access table will not affect the data in another table. If you change your make-table query back to a select query, you can edit the query in datasheet view and update the table that way.

Caveat: if your query contains multiple tables joined together, make sure you are editing the column you think you are updating in the underlying table you think you are updating!!!

 

[killerleaf]

I think I am going to just give up on this....or, can someone help me figure out how to do this?

I need the Main Patient Table to have all of the updated information.

However, I need a way to just have a list and be able to update the patients that are currently on the service. And the updates to go to the Main Patient Table.

 

[Joe4]

I think you may be going about this is the wrong manner (I assume that that fields you want updated aren't the key fields which are used to link the table together).

Why are you creating a second table from the first, if you want to update records in BOTH tables? If you just want to display current clients, put a flag field on the main table indicating if it is a current client or not. Then you can use a query to select/display the current clients. That way you are only updating/maintaining one set of records instead of two.

 

[killerleaf]

ray: ray:
this is what happens when you send someone to a couple of acess classes; then they want you to redesign the wheel!

Anyway, we have a perfectly good database, that works great; except it is on a mac. an OLD mac. since the powers that be do not want to pay anyone to do this professionally, it has fallen to me to come up with something in access so that we can do this on the pc's. and the network.

there are others who will be doing the data entry, and I had the noble thought of trying to get this to work as close to the original as possible, alleveating culture shock as much as possible.

I have tried 5 times, and have hit points where I had to chuck the whole thing and start over.

SOOO...pity party over....sorry.

Here is what I am trying to do...please advise the best way to do this.

Main patient table has to have all information. Daily updates are made. These need to be recorded to the records in the Main table. Updates can involve almost any of the fields, including names (spelling errors) and medical record numbers (typos), which are the consistant fields throughout.

The person doing the updates needs to have a list to work off of that shows which patients are on the service that day, (because there are always problems getting the ending number of patients from the day before to match the starting number for today) It would be easiest if the person doing the updating could work off of the list for the updates, so that they did not have to flip back and forth, and in all probability, lose track of someone.

How do I accomplish this? any all suggestions welcome

 

[Joe4]

Without knowing the exact structure of the database, or your level of expertise, it is really difficult to give precise advice. However, see if this helps.

In general, in Access it is advised that you don't update records directly through the Tables (as a matter as fact, some people may even go as far as to tell you to hide/restrict the Tables from view of the users). Tables should be used to simply hold your data, and set up the rules for your fields. You also want to link your tables using Tools | Relationships.

To update/view records, you usually want to use Forms. You can create an AutoForm from a Table or Query and then modify it. Your forms can link directly to tables, or they can link to queries. You can set up different forms for different groups that access different records from the same table.

For example, let's say that we have two groups, one that works on NY companies and one that works on CA companies. We don't want (or need) two different tables. We simply have one table (make sure it has a Sstate field). Then we create two queries; one in which we set the Criteria on the State field to equal "CA", and one in which we set the Criteria = "NY".

Now we can set up two different forms based on these two queries, one for NY and one for CA. So depending on which form they are in, they will see different records, but they all update the same main table.

Similarly, you should be able to set up a query to only pull the Current Client records you want from your main table instead of creating a new table and then trying to coordinate it to the main table.

 

[killerleaf]

ok, cool, gotcha, I understand now. my expertise level is right above newborn. (but not much)...so please bear with me. I tried what you suggested, and it will work fine, the users are just going to have to get used to the way it looks. I have a feeling that for the most part, I am trying to make this way too complicated!

I really appreciate the help!!