2 tables - query - append table

[qaccess]

I have two tables [A and B]. I combine these tables with a query. I create a new table [C] with data from the query with append...So far this works..

My problem: I change data in the first two tables [A and B].. How can I auto update the data in the new table [C]

Please a solution!

 

[Len Boorman]

Why are you creating C ?.

If you have a good reason then you need an event procedure so that when you amend/add a record to delete all records in C and re-create them

Again I ask why create C to start with. All the info is available from A and B

Why duplicate

L

 

[qaccess]

auto update table

Dear Len,

I try to create a soccer competition. I attached a test.zip containing the database..

one table contains division of 12 clubs [tbl_indeling]
one table contains diagram [tbl_speelschema]

i combine these tables in a query [qr_programma_nr]
now i attach date when the matches will be played [tbl_speeldata] to [qr_programma_le]

from here i create an append query [tbl_programma] with 132 matches. Now I can change edit delete something if i want..But how can this table [tbl_programma] autoupdate when I change something in [tbl_indeling] or [tbl_speelschema]

Thanks

 

[Len Boorman]

Okay

Had a look at the zip but unfortunately I am not fluent in your language so a bit of guesswork.

Basically I believe that you have a single list of Football Clubs and there will be a total of 132 matches.

So the table [tbl_indeling] will have a relationship 1 to many with the table [tbl_speelschema] to both the participating teams in each match.

The tbl_indeling should contain only that information that truly relates to that particular Team (Name, Home Stadium etc) None of the information in this table should really influence the tbl_speelschema.

The tbl_speelschema should have information that relates only to each fixture, such as start time, result. etc

This process in known as Normalisation

This way you have data independance and do not have data update situations.

You have created the fixture list and need to review what you would change in the tbl_indeling that affects tbl_speelschema.

Probable result of this exercise is to identify those items that should not be in tbl_indeling.

HTH

L

 

[qaccess]

update query

Hi Len,

can you explain me what normalisation is.. I am not so experienced in Access..and i made one mistake. I like to change the data in [tbl_indeling] and [tbl_speeldata] Not [tbl_speelschema] and then this gives an auto update.

You can view my website at http://www.gjs-gorinchem.nl

My big problem is that during the competition at the lower soccerteams sometimes a club will be replaced with another club. [tbl_indeling]![clubnaam

So i have to update [tbl_programma] this gives me mostly problems with data i did changed before.

Do i have to change the structure of the database?

 

[Len Boorman]

Normalisation is a rather large subject but basically it seeks to ensure that all fields (attributes) within a table (Entity or relation) are fully dependant upon the primary key.

tbl Team might be the Entity with attributes of say

Name (possible the primary key
Stadium
Manager
Web site
etc

All of these relate directly to the Team.

If you were to add say kick off time then this does not fully relate to the Team. It actually relates to a particular match.

That's a very limited explanation. It actually derives from Functional dependancies. Use the Searce and you will find lots of stuff on the Forum. Look for posts by Pat Hartman


Now you example

If a match is changed then you need to change the match table. I assume that the Team already exists.

Similarly of a start time changes you only need to change that in the match table.


I presume that

If a Team ceases and it replaced by another Team then you need to add the new team and change the match table to remove the old team from matches they have not played and substitute the new team.

This could be done by coding but is not simple really because what happens if two teams drop out at the same time.

Concentraye on getting the data structure right first so that you are able to make the changes you want with the minimum of effort. After that consider automating the task

Len

 

[qaccess]

Thanks Len

Thanks Len

I will explore the forum...I figured out that creating a database is not that simple, but if it works...It is a great tool..

 

[Len Boorman]

You are absolutely correct in that databases are not simple. There is a awful lot behind the technology. It seems like a mountain. But remember that to climb Everest basically you do it one step at a time

Each step gets you closer to the top but as many people will tell you the more you learn then the more there is still to learn

len