Data Entered Moved to another Table

S

Sonya

Below is my delimma:
Table 1: Main Table - Product #; Product Description;
Table 2: Sub Table - Item #; Item Description; Revised?; Revised Date;
(other fields are price and the different locations where these items are
sent).
Table 2:
Item # Item Desc Revised? Revised Date
1101 Coke Price (1.00 to 1.25) 9/15/05
1105 Sprite New Item 9/12/05
Everytime items have a revision I type the information in the table over the
previous entry. So, the previous entry disappears and I have no record of
the revision. If after a couple of months I haven't had a revision on an
item, I will delete the revised data out of table; But I would like a 3rd
table to consists of all my revisions for each item. So I can look back and
see when a price was raised and etc. How would I accomplish this? I hope I
haven't confused you to much. I will be off work for the next couple of
days, but anxiously await for a reply. Thanks in advance for your help.
 
G

Graham Mandeno

Hi Sonya

Why do you want to move the old revisions to another table? And Why are you
typing over the previous entry instead of creating a new record?

I'm not sure what the relationship is between your two tables. Are
Product#/ProductDescription the same as Item#/ItemDescription? If so, then
your Revisions table needs to look like this:

ProductNum RevisionDate RevisionDescription
1101 9/2/05 New Item
1105 9/12/05 New Item
1101 9/15/05 Price (1.00 to 1.25)
....etc

That way, you can see a record of ALL your revisions in the same place. If
you want to limit the view (say to just the previous month) then simply
create a query that filters on RevisionDate.

The ProductNum fields in the two tables should be linked in a one-to-many
relationship.
 
J

John Vinson

Below is my delimma:
Table 1: Main Table - Product #; Product Description;
Table 2: Sub Table - Item #; Item Description; Revised?; Revised Date;
(other fields are price and the different locations where these items are
sent).
Table 2:
Item # Item Desc Revised? Revised Date
1101 Coke Price (1.00 to 1.25) 9/15/05
1105 Sprite New Item 9/12/05
Everytime items have a revision I type the information in the table over the
previous entry. So, the previous entry disappears and I have no record of
the revision. If after a couple of months I haven't had a revision on an
item, I will delete the revised data out of table; But I would like a 3rd
table to consists of all my revisions for each item. So I can look back and
see when a price was raised and etc. How would I accomplish this? I hope I
haven't confused you to much. I will be off work for the next couple of
days, but anxiously await for a reply. Thanks in advance for your help.
Click to expand...

You really only need two tables.

Rather than overwriting the revision information in Table2, simply add
a new record. Table2 should be related one-to-many to Table1, and it
should have as many records for each item as there have been revisions
(of course, this might mean there are no records if the item has not
been revised). You'll need to select BOTH the Item # and Revised Date
fields as a joint two-field Primary Key.

You can create a Query to retrieve the most recent record by using a
criterion on [Revised Date] of

=(SELECT Max([Revised Date] FROM [Table 2] AS X WHERE X.[Item #] =
[Table 1].[Product #])

One question: are the Product # and the Item # the same value in the
two tables (i.e. the linking field)? And is the [Item Desc] field in
Table2 always the same as the [Product Description] in Table1?

And of more concern - you say "other fields are... the different
locations where these items are sent". This sounds like there should
be a distribution TABLE with multiple records, not multiple fields in
either Table1 or Table2. Am I misunderstanding?

John W. Vinson[MVP]
 
S

Sonya

My first table is canteen list:
Product # Product Description
1 Cigarettes
2 Chew
3 Candy
My second table breaks down all the canteen items into each product # /
Description above and is named canteen price list:
Item # Item Desc Pk Sz Price Revised What? Revised Date
1101 Marlboro 1 ea 2.43 Price 2.41 to 2.43 9/15/05
1102 Salem 1 ea 2.43
1103 Snickers 1 ea 0.60 New Item 9/15/05
I have a one to many relationship w/ the canteen list product desc being the
one and canteen price list product desc being the many. The primary key on
table 1 is prod desc and on table 2 is item #. Is there a way for the
revision information to automatically be copied over to a revision table each
time I enter this information?

John Vinson said:
Below is my delimma:
Table 1: Main Table - Product #; Product Description;
Table 2: Sub Table - Item #; Item Description; Revised?; Revised Date;
(other fields are price and the different locations where these items are
sent).
Table 2:
Item # Item Desc Revised? Revised Date
1101 Coke Price (1.00 to 1.25) 9/15/05
1105 Sprite New Item 9/12/05
Everytime items have a revision I type the information in the table over the
previous entry. So, the previous entry disappears and I have no record of
the revision. If after a couple of months I haven't had a revision on an
item, I will delete the revised data out of table; But I would like a 3rd
table to consists of all my revisions for each item. So I can look back and
see when a price was raised and etc. How would I accomplish this? I hope I
haven't confused you to much. I will be off work for the next couple of
days, but anxiously await for a reply. Thanks in advance for your help.
Click to expand...

You really only need two tables.

Rather than overwriting the revision information in Table2, simply add
a new record. Table2 should be related one-to-many to Table1, and it
should have as many records for each item as there have been revisions
(of course, this might mean there are no records if the item has not
been revised). You'll need to select BOTH the Item # and Revised Date
fields as a joint two-field Primary Key.

You can create a Query to retrieve the most recent record by using a
criterion on [Revised Date] of

=(SELECT Max([Revised Date] FROM [Table 2] AS X WHERE X.[Item #] =
[Table 1].[Product #])

One question: are the Product # and the Item # the same value in the
two tables (i.e. the linking field)? And is the [Item Desc] field in
Table2 always the same as the [Product Description] in Table1?

And of more concern - you say "other fields are... the different
locations where these items are sent". This sounds like there should
be a distribution TABLE with multiple records, not multiple fields in
either Table1 or Table2. Am I misunderstanding?

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

My first table is canteen list:
Product # Product Description
1 Cigarettes
2 Chew
3 Candy
My second table breaks down all the canteen items into each product # /
Description above and is named canteen price list:
Item # Item Desc Pk Sz Price Revised What? Revised Date
1101 Marlboro 1 ea 2.43 Price 2.41 to 2.43 9/15/05
1102 Salem 1 ea 2.43
1103 Snickers 1 ea 0.60 New Item 9/15/05
I have a one to many relationship w/ the canteen list product desc being the
one and canteen price list product desc being the many. The primary key on
table 1 is prod desc and on table 2 is item #. Is there a way for the
revision information to automatically be copied over to a revision table each
time I enter this information?
Click to expand...

I'm not seeing any connecting field between the two tables. How can
Access identify that "Salem" should be linked to Product # 1? Exactly
wnat information do you want to be copied - and where? I'm still not
really understanding your structure here!

John W. Vinson[MVP]
 
Top