Update query

[Jennifer]

I have 2 tables...
Table 1: has two fields- "Reports" and "Current Period"- current period is
yes/no.
Table 2: has 13 fields- "reports" and "PD 01-PD12"

I want to run an update query...I want to use Table 1 to update Table 2
depending on the Period...
I was hoping in my update query to link by report, and update table 2 to
"Current Period" in table 1. Is there any way not to specify the "FIELD" in
the update query? For Example, in some queries you can use [] so it prompts
parameters to be entered, can you do something like this to prompt what field
it should be updated to?

I hope what I want to achieve makes some kind of sense. Please let me know
if this can be done.

Thank You,
Jennifer

 

[John W. Vinson]

I have 2 tables...
Table 1: has two fields- "Reports" and "Current Period"- current period is
yes/no.
Table 2: has 13 fields- "reports" and "PD 01-PD12"

Then your Table2 is *incorrectly designed*. Storing data - a period it seems -
in a fieldname is fine for a spreadsheet but *simply wrong* for a relational
table. "Fields are expensive, records are cheap".

I want to run an update query...I want to use Table 1 to update Table 2
depending on the Period...

If the period is Yes/No how does Access identify "the current period"?

I was hoping in my update query to link by report, and update table 2 to
"Current Period" in table 1. Is there any way not to specify the "FIELD" in
the update query? For Example, in some queries you can use [] so it prompts
parameters to be entered, can you do something like this to prompt what field
it should be updated to?

I hope what I want to achieve makes some kind of sense. Please let me know
if this can be done.

Stop. Step back. You're going about this in the wrong way, it appears!

You have a One (report) to Many (periods) relationship embedded within a
single record. Consider instead using a one to many relationship. What
Entities - real-life persons, things, or events - are represented by your two
tables? What is a "reports" and what is a "period", and what is the meaning
(again, in the real world) of "current period"?

 

[Jennifer]

Let me try to better explain this...
Table 1: Reports Current Period
Payback Report X(represents
yes/no check box)
Vendor Hold Report X
Time shees X

Table 2: Reports PD01 PD02
Payback Report X
Vendor Hold Report X
Time Sheets X

This was originally the update query I was trying to use but would not work,
because if you add a report or drop a report on Table 2, it doesn't want to
work right. (This update query DOES NOT include Table 1---Table 1 was created
to try something different)
UPDATE [DSD- Month Close Reports] SET [DSD- Month Close Reports].[PD 02] =
[DSD- Month Close Reports]![PD 01], [DSD- Month Close Reports].[PD 03] =
[DSD- Month Close Reports]![PD 02], [DSD- Month Close Reports].[PD 04] =
[DSD- Month Close Reports]![PD 03], [DSD- Month Close Reports].[PD 05] =
[DSD- Month Close Reports]![PD 04], [DSD- Month Close Reports].[PD 06] =
[DSD- Month Close Reports]![PD 05], [DSD- Month Close Reports].[PD 07] =
[DSD- Month Close Reports]![PD 06], [DSD- Month Close Reports].[PD 08] =
[DSD- Month Close Reports]![PD 07], [DSD- Month Close Reports].[PD 09] =
[DSD- Month Close Reports]![PD 08], [DSD- Month Close Reports].[PD 10] =
[DSD- Month Close Reports]![PD 09], [DSD- Month Close Reports].[PD 11] =
[DSD- Month Close Reports]![PD 10], [DSD- Month Close Reports].[PD 12] =
[DSD- Month Close Reports]![PD 11];

Being the above SQL didn't work correctly, I created Table 1. I wanted to do
an update query...
Field: []
Table: Table 2
Update To: [Table1]![Current Period]

I was hoping with the field being [], it would prompt me to enter a
parameter and would enter PD02 so it would drop the information into Table 2,
column PD02. But that's not how it worked.

Hopefully this better explained what I'm trying to accomplish. Any help is
appreciated.

Thank You

I have 2 tables...
Table 1: has two fields- "Reports" and "Current Period"- current period is
yes/no.
Table 2: has 13 fields- "reports" and "PD 01-PD12"

Then your Table2 is *incorrectly designed*. Storing data - a period it seems -
in a fieldname is fine for a spreadsheet but *simply wrong* for a relational
table. "Fields are expensive, records are cheap".

I want to run an update query...I want to use Table 1 to update Table 2
depending on the Period...

If the period is Yes/No how does Access identify "the current period"?

I was hoping in my update query to link by report, and update table 2 to
"Current Period" in table 1. Is there any way not to specify the "FIELD" in
the update query? For Example, in some queries you can use [] so it prompts
parameters to be entered, can you do something like this to prompt what field
it should be updated to?

I hope what I want to achieve makes some kind of sense. Please let me know
if this can be done.

Stop. Step back. You're going about this in the wrong way, it appears!

You have a One (report) to Many (periods) relationship embedded within a
single record. Consider instead using a one to many relationship. What
Entities - real-life persons, things, or events - are represented by your two
tables? What is a "reports" and what is a "period", and what is the meaning
(again, in the real world) of "current period"?

 

[John W. Vinson]

Let me try to better explain this...

Again:

The reason you're having trouble is that your table design IS WRONG.

Having twelve fields for the twelve periods IS WRONG.

Running an update query to "left shift" these twelve fields IS WRONG.

Stop. You're just digging yourself into a deeper hole by letting your desired
report format drive your table design. This table should simply not exist, in
its current form; instead you should generate this display using a Crosstab
query.

 

[Pete D.]

Hi,

You need to take a look at what a relational database is. Right now you are
thinking in Excel and if Access was Excel you would be brilliant at it.
Fact is, without stepping back and looking at your table design you will
never be satisfied and will begin to think that Access is a crap program.
In access you must think what data I need to store and use. Not what do I
want the report or form to look like. For instance; if I have a student I
should only have to enter his/her name once, a class/period once. All
information will come from that information and I'll never have to type it
again. Let's look at excel, I have student George and he is going to take
six classes. In excel, and rightly so you would enter George six times with
all his classes. In access you would enter George once and select the six
classes he will take. Those classes will also only be entered once each.
You will relate each class to George and a tiny little table will relate
them with a couple of key fields. Try taking a breather and read about
relational databases and pay close attention to the relationships. Once you
understand this and what relationships are you will wonder why you worked so
hard before. Some sites to visit



Simple definition no one understands
http://en.wikipedia.org/wiki/Relational_database

A little easier to understand but bad example for Access
http://computer.howstuffworks.com/question599.htm

Some pictures that will help grasp the idea
http://www.databasedev.co.uk/data_models.html

Let me try to better explain this...
Table 1: Reports Current Period
Payback Report X(represents
yes/no check box)
Vendor Hold Report X
Time shees X

Table 2: Reports PD01 PD02
Payback Report X
Vendor Hold Report X
Time Sheets X

This was originally the update query I was trying to use but would not
work,
because if you add a report or drop a report on Table 2, it doesn't want
to
work right. (This update query DOES NOT include Table 1---Table 1 was
created
to try something different)
UPDATE [DSD- Month Close Reports] SET [DSD- Month Close Reports].[PD 02] =
[DSD- Month Close Reports]![PD 01], [DSD- Month Close Reports].[PD 03] =
[DSD- Month Close Reports]![PD 02], [DSD- Month Close Reports].[PD 04] =
[DSD- Month Close Reports]![PD 03], [DSD- Month Close Reports].[PD 05] =
[DSD- Month Close Reports]![PD 04], [DSD- Month Close Reports].[PD 06] =
[DSD- Month Close Reports]![PD 05], [DSD- Month Close Reports].[PD 07] =
[DSD- Month Close Reports]![PD 06], [DSD- Month Close Reports].[PD 08] =
[DSD- Month Close Reports]![PD 07], [DSD- Month Close Reports].[PD 09] =
[DSD- Month Close Reports]![PD 08], [DSD- Month Close Reports].[PD 10] =
[DSD- Month Close Reports]![PD 09], [DSD- Month Close Reports].[PD 11] =
[DSD- Month Close Reports]![PD 10], [DSD- Month Close Reports].[PD 12] =
[DSD- Month Close Reports]![PD 11];

Being the above SQL didn't work correctly, I created Table 1. I wanted to
do
an update query...
Field: []
Table: Table 2
Update To: [Table1]![Current Period]

I was hoping with the field being [], it would prompt me to enter a
parameter and would enter PD02 so it would drop the information into Table
2,
column PD02. But that's not how it worked.

Hopefully this better explained what I'm trying to accomplish. Any help is
appreciated.

Thank You

I have 2 tables...
Table 1: has two fields- "Reports" and "Current Period"- current period
is
yes/no.
Table 2: has 13 fields- "reports" and "PD 01-PD12"

Then your Table2 is *incorrectly designed*. Storing data - a period it
seems -
in a fieldname is fine for a spreadsheet but *simply wrong* for a
relational
table. "Fields are expensive, records are cheap".

I want to run an update query...I want to use Table 1 to update Table 2
depending on the Period...

If the period is Yes/No how does Access identify "the current period"?

I was hoping in my update query to link by report, and update table 2 to
"Current Period" in table 1. Is there any way not to specify the "FIELD"
in
the update query? For Example, in some queries you can use [] so it
prompts
parameters to be entered, can you do something like this to prompt what
field
it should be updated to?

I hope what I want to achieve makes some kind of sense. Please let me
know
if this can be done.

Stop. Step back. You're going about this in the wrong way, it appears!

You have a One (report) to Many (periods) relationship embedded within a
single record. Consider instead using a one to many relationship. What
Entities - real-life persons, things, or events - are represented by your
two
tables? What is a "reports" and what is a "period", and what is the
meaning
(again, in the real world) of "current period"?