How can I update identical fields in several tables by only entering in one?

[Daedalus]

Hello all you brillaint people who might be able to help me.

I've recently become treasurer for a small club and need to create a membership database.

Now I'm almost a complete newbie at Databases - but i have managed to make small inroads.

What I need it some way to make it so that when I enter a new record in the Membership Table, it is also created and filled out in the common fields in the membership Dues Table - they have a name and number field in common (Brcause the Dues page needs to be printed and posted at the club).

If helpful I can mail the empty database, however much is in Danish.

[NeoPa]

The short answer to that is that it's not a good approach to synchronise updates.
What you should aim to do is store the information only once.
Then you use queries that connect tables together, to find and show all the info you need.
For instance, if you had four people living in a house and they each used the same phone,. You wouldn't store the phone number with each person. Instead you'd connect each person to a 'House' table and store the phone number in there.

If you need help doing this just post the question in here.
You will need to provide more detailed information though. Databases and coding depend very heavily on the details (tables; fields; logical connections between the tables; etc).

[Daedalus]

Ok - here's a breakdown of what I'm trying to accomplish:

One table is to include Number, Name, Adress, Telephone number, E-Mail, Type of Membership and Method of payment.

This is the membership list.

One other table is to include Number, Name and a column for each month.

This is the Membership Dues list.

They have the name and number column in common now because the dues list needs to be published at the club - and I need all the info at home so it is also in the other list.

I have created the two tables as they are written here, and have created a form to allow me to enter a new member into the Membership List Table. I have also managed(somehow) to create a relationship link between the two so that it is possible via a submenu to see the dues list in the membership list by click the small + that appears.


Ideally I would like it so that when I create a new member in the first table it is also generated in the second table. I can see how a query might be best for getting a printable list - but they still need a column in common, and I still have to create the second one by hand.

[NeoPa]

Ok - here's a breakdown of what I'm trying to accomplish:

One table is to include Number, Name, Adress, Telephone number, E-Mail, Type of Membership and Method of payment.

This is the membership list.

One other table is to include Number, Name and a column for each month.

This is the Membership Dues list.

They have the name and number column in common now because the dues list needs to be published at the club - and I need all the info at home so it is also in the other list.

I have created the two tables as they are written here, and have created a form to allow me to enter a new member into the Membership List Table. I have also managed(somehow) to create a relationship link between the two so that it is possible via a submenu to see the dues list in the membership list by click the small + that appears.


Ideally I would like it so that when I create a new member in the first table it is also generated in the second table. I can see how a query might be best for getting a printable list - but they still need a column in common, and I still have to create the second one by hand.

You can link the two tables (in Relationships as well as any query) by the Number field (I would call it MemberID rather than Number though for clarity).
Remove the Name field from the Membership Dues table.
Create a query (qryMembershipDues) with the following SQL (or something similar which exactly matches your field and table names).

SELECT [M].[MemberID], [M].[Name], [M].[Address], [M].[Telephone number], 
   [M].[E-Mail], [M].[Type of Membership], [M].[Method of payment],
   [D].[Column]
FROM [Membership] AS [M] INNER JOIN [Membership Dues] AS [D] ON
   [M].[MemberID]=[D].[MemberID]

You can now build a form (and subform) in your database, on top of this query, by using the Forms Wizard.
This can automatically link the parent form (membership) with the sub-form (membership Dues) and show a correct logical connection between the two.

Before you do any of this though, I would advise creating a one-to-many reference between the two tables with full referential integrity enforced (including propagation of updates and deletes).

PS. I would advise making a backup of your database file before making any such fundamental changes. This would be no exception. Good luck & let us know how you get on.

[Daedalus]

Well, sofar I get the query granting me the name and number for the printable dues page I ned to generate - but all the columns for the 12 months are missing.

The page I need to generate should look something like this:

Number, Name, Jan, feb, Mar, Apr, May, June, July, Aug, Sept, Oct, Nov, Dec

In the field names across - and each members data listed below.

the name field needs to be taken from Table 1: Membership List - and the rest taken from Table 2: Membership Dues.

The above listed SQL does not do this.

And I can only get it to allow me to create a one-to-one relationship between the number fields in the two tables.

ANY help is greatly appriciated btw. I know it is most likely me that is dense on this.

[PEB]

Hi

If you have in your due table a column where you store the information of the month, named Month, ie

So the only thing you can do is using a Crosstab query..

This can be created from Queries->Query design

From the menu u choose Query->Crosstab Query

Than something special happens... It appears 2 complementary rows:
Group By
And
Row with those choices: Row heading, Column Heading, Value

Mark under all of your fields Row Heading except the Month. Under it select Column Heading

Under the column duty select Value and choose instaed group By, the function Sum

When you display the query in datasheet you will see the query as u want to see it!

[NeoPa]

What PEB says is certainly a possibility, but there are others.
I didn't include any of those field in the query I posted because I'd not been told about them :S.
The best thing is for you to post details of the tables you use with their exact names and the names and types of all the fields in them.
That way we know what we're dealing with and we can come up with an appropriate solution.

[NeoPa]

Another way, if you feel up to it, is to take the SQL I posted and paste it into a new query (Create new query; go into SQL view; Paste in SQL).
When that's done, view it in Design view and make the changes you know you need.
If you hit difficulties then try the other method (post all the details requested in here).