How do I update related tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a database in order to track 10 weeks of information. I have
created forms to input information into current weekly tables. However, I
have been unsuccessful at transferring the information from the current week
table to the 10 week table in the appropriate week. I would appreciate any
suggestions. Sandy
 
On Sun, 14 Nov 2004 13:48:01 -0800, "Sandy Marshall" <Sandy
I have created a database in order to track 10 weeks of information. I have
created forms to input information into current weekly tables. However, I
have been unsuccessful at transferring the information from the current week
table to the 10 week table in the appropriate week. I would appreciate any
suggestions. Sandy
Click to expand...

What is the structure of your tables? You should NEVER need to
"transfer data from the current week table to the 10 week table" - you
should store the data once, and once only, and then use queries to
summarize it!

What is your "ten week table" like? Do you have fields for Week1,
Week2, Week3? or (even worse) fields with dates as fieldnames?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John Vinson said:
On Sun, 14 Nov 2004 13:48:01 -0800, "Sandy Marshall" <Sandy


What is the structure of your tables? You should NEVER need to
"transfer data from the current week table to the 10 week table" - you
should store the data once, and once only, and then use queries to
summarize it!

What is your "ten week table" like? Do you have fields for Week1,
Week2, Week3? or (even worse) fields with dates as fieldnames?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
John,
The fields in the table are Wk1 through Wk10. My form uses current
information only to print reports. I have tried to set up an update query to
move the current info to the wk1.... but the query deletes the info from the
current info and doesn't move it to wk1 and so on. The database is set up
for a dart league and the handicaps are based on 10 wks of play. Should I do
something different?
Sandy
 
On Sun, 14 Nov 2004 17:56:02 -0800, "Sandy Marshall" <Sandy
The fields in the table are Wk1 through Wk10. My form uses current
information only to print reports. I have tried to set up an update query to
move the current info to the wk1.... but the query deletes the info from the
current info and doesn't move it to wk1 and so on. The database is set up
for a dart league and the handicaps are based on 10 wks of play. Should I do
something different?
Click to expand...

Yes, you should. This table is simply WRONG. It is not a table, it is
a spreadsheet!

Instead, create a Crosstab Query and base your report on that Query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Yes, you should. This table is simply WRONG. It is not a table, it is
a spreadsheet!

Instead, create a Crosstab Query and base your report on that Query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

To expand on this slightly, you need to record the scores (or
whatever) in a single Table, probably linked to a second table with
player information. The scores Table will have a field for "week
number", which could either be a simple date field containing the
"week commencing" date, or an integer field with serial week numbers.
You will then have Queries to produce the Reports you need, probably
including a Crosstab query to summarise scores by weeks for the last
10 weeks.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Peter R. Fletcher said:
To expand on this slightly, you need to record the scores (or
whatever) in a single Table, probably linked to a second table with
player information. The scores Table will have a field for "week
number", which could either be a simple date field containing the
"week commencing" date, or an integer field with serial week numbers.
You will then have Queries to produce the Reports you need, probably
including a Crosstab query to summarise scores by weeks for the last
10 weeks.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
Is there somewhere I can find sample queries of this sort? I have never
tried a crosstab query.
Sandy
 
Is there somewhere I can find sample queries of this sort? I have never
tried a crosstab query.
Sandy
Click to expand...

I don't use them much, either, since I don't generally write the sort
of applications that need them. Have you checked Northwind (the big
sample database application that comes with Access) - it has one of
most things!

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top