Enter number get text

[Husky]

I finally got around to categorizing my Home Improvement VCR collection.

I have the data base layout
Episode title Air_Date Prod Season Num
1 pilot 9/17/1991 63294 1 1
for all the episodes.

I'm currently using a form created by the wizard with the standard vcr
collection template to input the data as they appear on the VCR's.
I didn't start taping with episode one, waited for reruns, so the vcr order
isn't the same as it appeared for the 1st 8 seasons.
ep1 could be on tape 10, ep2 on tape 5, etc..

I have the ep guide, so I'd like to know how to make the rest of the form input
the data from the guide table, if I input the episode number ? Save a lot of
typing.

IOW: auto fill in the individual form records from the episode guide table,
based on the episode number that I would type in.

 

[Steve Schapel]

Husky,

If you have repetitive data, it normally means you need related tables
to reflect the one-to-many nature of the relationship between various
parts of your data.

I am not 100% clear about how you have it set up, as I didn't see any
reference to the tape number or the ep guide in the example data you
showed us. However, this article may be of help to you...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[Husky]

Husky,

If you have repetitive data, it normally means you need related tables
to reflect the one-to-many nature of the relationship between various
parts of your data.

I am not 100% clear about how you have it set up, as I didn't see any
reference to the tape number or the ep guide in the example data you
showed us. However, this article may be of help to you...
http://accesstips.datamanagementsolutions.biz/lookup.htm

there's 2 tables.
table the episode guide1 is

Episode title Air_Date Prod Season Num
1 pilot 9/17/1991 63294 1 1

and the vcr input form is
"VideoCollectionID","Tape_Number","Episode","MovieTitle","ActressID","ActorID","YearReleased","Rating","Subject","Length","Review","Notes"
1,7,177,"Whitewater",,,,,,,,"White water rafting"
Whole lot of extra garbage from using the built in template for video
collection.
As you can see, for now I'm only using the fields below.

"VideoCollectionID","Tape_Number","Episode","MovieTitle","Notes"
1,7,177,"Whitewater",,,,,,,,"White water rafting"

Oddly it does seem to be doing something similar to what I wanted it to with a
sub table after I input the data ie: On VCR tape 4, I had taped the same show
twice on it, and so it appeared on tape 4, as record 16, and record 30.
And the above was filled in, in the vcr sub table. using the 5 fields above.

But I want the reverse to happen. When I enter the episode number, I want it to
fill in the form with all the extra info. How Do I get the info from the sub
table to appear in the form ?
Actually the only data needing filled in automatically is the title.




But I have no idea how to use the sub table. I do have the Access 2000 complete
reference, but can't find anything about filling in the blanks. It looked like
a lookup Query was the route to go, but the reference seemed to drop the
subject just after it mentioned it.

 

[Steve Schapel]

Husky,

What is the nature of the relationship between the data in these two tables?

 

[Husky]

for simplicity, I'll call the entire guide, all episodes as table 1.
and the db I'm making of vcr tapes as vcrs.

the things that do agree between table 1 and vcrs' is the episode number is the
same, the titles are the same. Only actual difference is in vcr's the notes and
videocollection id.
Oh the episode in table 1 is the primary key, and videocollectionid is the
primary in vcrs.

I'm building vcrs for a printout and semi permanent [lost way too much data to
consider the word permanent] record so I can find the episode I'm looking for
without browsing thru all 19 vcr tapes.
Actually, I'm doing it so I can actually sort them while putting them on DVD in
order.
And a printout would be nice to have handy.

If that doesn't explain the nature, then I don't understand the question.
The guide is a chronological list with summary of all the episodes.
vcr's is the list I'm making.

The spine and face of the VCR labels didn't have enough room to write in all
the titles you can get on a 120 minute tape. I have anywhere from 10-15
episodes per tape. so they're only listed on the vcrs by episode number. Those
tapes that even have a face or spine label.

Last years hurricane I lost a few tapes with water damage. I suspect a DVD will
be a bit more rugged, so moving everything to DVD.
Plus the space saving advantage.
Disadvantage, have to find the one I want from the printout.
jewel cases have no room for labels on the side.

 

[Steve Schapel]

Husky,

I think your table design needs to be reviewed before anything else. It
will be difficult to manage the information as to which Episodes of
which Titles are recorded on which Tapes, with your existing structure.
It seems to me that both the tables you have described relate to the
same information... each record is information about one Episode, am I
right? If so, the two tables should be combined. If you have Episode
field as the primary key, it means that this can not be duplicated... is
this what you intended? I at first imagined that this referred to an
episode number of a particular series, so for example each Title might
have an Episode 1, Episode 2, etc. But apparently not? In that case,
you can make your Episodes table like this...
Episode
Tape_Number
Title
Air_Date
Prod
Season
Num
Notes

.... and that's it! From this information, you should be able to get
your list of Episodes on each Tape, plus be able to easily locate which
Tape contains which Episode, or which Tapes contain Episodes of which
Titles, etc.

 

[Husky]

Husky,

I think your table design needs to be reviewed before anything else. It
will be difficult to manage the information as to which Episodes of
which Titles are recorded on which Tapes, with your existing structure.
It seems to me that both the tables you have described relate to the
same information... each record is information about one Episode, am I
right? If so, the two tables should be combined. If you have Episode
field as the primary key, it means that this can not be duplicated... is
this what you intended? I at first imagined that this referred to an
episode number of a particular series, so for example each Title might
have an Episode 1, Episode 2, etc. But apparently not? In that case,
you can make your Episodes table like this...
Episode
Tape_Number
Title
Air_Date
Prod
Season
Num
Notes

... and that's it! From this information, you should be able to get
your list of Episodes on each Tape, plus be able to easily locate which
Tape contains which Episode, or which Tapes contain Episodes of which
Titles, etc.

Well I got something, I made a query from that web link page I got combining
both tables. The form created a 2 tier form with the vcr table [that I'm
making] on the bottom. Works to some degree.

ie The title from episodes is the same in both tables, The info in the VCR
table that is different is the notes field [synopsis of the episode], the VCR
ID [autonumber, works for me], and tape number field. These fields don't exist
in the episodes table.

This new form is almost what I'm trying to accomplish. It's also updating the
individual VCR table correctly. The only thing that's not happening in the VCR
table that needs work is adding the title of the episode [automatically].
It shows the title on the form, but in the vcr table the titles are blank, not
updated.
I don't type the title in. I know I can, but there's no reason I should, when
it already exists in the episode list. But when I make the printout, I'll be
using this VCR list I made and having the titles on the printout is a
necessity.

I know, just do an extra copy & paste as I am doing now for the notes field.
But it's a machine, it's designed to do tedious repetitive work automatically.
I just need to figure out the formula for the VCR sub form to get it to extract
the title from the episode list based on the episode number. And transfer that
title from the episode table title field, to the vcr table title field.

Here's just a sample of the control source I used that failed: I tried several
variations of this and got nowhere.
VCR [title]=episodes[title]&episodes[episode]=vcr[episode]
Just got the #NAME? error.

 

[Steve Schapel]

Husky,

As I intimated before, I would recommend that you try to establish a
valid table structure before you even start to think about any aspects
of form design.

 

[Husky]

Husky,

As I intimated before, I would recommend that you try to establish a
valid table structure before you even start to think about any aspects
of form design.

Obviously I don't understand the question then. Valid table structure ?

I have 2 tables.
VCRs, and episodes.

episodes
Episode,title,Air_Date,Prod,Season,Num

and

VCRs
VideoCollectionID,Tape_Number,Episode,MovieTitle,ActressID,ActorID,YearReleased,Rating,Subject,Length,Review,Notes

I don't use most of the stuff in VCRs table. Just these
VideoCollectionID,Tape_Number,Episode,MovieTitle,Notes
The tables exist. As for structure, VCR's was made by access 2000, and episodes
was imported creating it's own structure.
You're going to have to explain what you mean by structure.

 

[Steve Schapel]

Husky,

Well, I did try to explain it before. I said:
"It seems to me that both the tables you have described relate to the
same information... each record is information about one Episode, am I
right? If so, the two tables should be combined. If you have Episode
field as the primary key, it means that this can not be duplicated... is
this what you intended? I at first imagined that this referred to an
episode number of a particular series, so for example each Title might
have an Episode 1, Episode 2, etc. But apparently not? In that case,
you can make your Episodes table like this...
Episode
Tape_Number
Title
Air_Date
Prod
Season
Num
Notes
.... and that's it! From this information, you should be able to get
your list of Episodes on each Tape, plus be able to easily locate which
Tape contains which Episode, or which Tapes contain Episodes of which
Titles, etc."

You did not appear to respond to this, so I guess that means you didn't
understand it, so my apologies. So here's the core of it. There are
certain principles that need to be applied to creating a database. One
is that the same information should not be replicated in different parts
of the data structure. Another is that a separate table should be used
for each subject that you are managing data about. You determine this
by asking this question about a table: "What does each record in this
table describe?" Unless I am misunderstanding you, each record in your
Episodes table describes an episode of a show. And each record of your
VCRs table describes an episode of a show. Please correct me if I am
wrong. In which case you will need to provide some examples of the data
so I can see what's going on. But otherwise, just make one table, which
combines the fields which you use from the two existing tables.

 

[Husky]

Husky,

Well, I did try to explain it before. I said:
"It seems to me that both the tables you have described relate to the
same information... each record is information about one Episode, am I
right? If so, the two tables should be combined. If you have Episode
field as the primary key, it means that this can not be duplicated... is
this what you intended? I at first imagined that this referred to an
episode number of a particular series, so for example each Title might
have an Episode 1, Episode 2, etc. But apparently not? In that case,
you can make your Episodes table like this...
Episode
Tape_Number
Title
Air_Date
Prod
Season
Num
Notes
... and that's it! From this information, you should be able to get
your list of Episodes on each Tape, plus be able to easily locate which
Tape contains which Episode, or which Tapes contain Episodes of which
Titles, etc."

You did not appear to respond to this, so I guess that means you didn't
understand it, so my apologies. So here's the core of it. There are
certain principles that need to be applied to creating a database. One
is that the same information should not be replicated in different parts
of the data structure. Another is that a separate table should be used
for each subject that you are managing data about. You determine this
by asking this question about a table: "What does each record in this
table describe?" Unless I am misunderstanding you, each record in your
Episodes table describes an episode of a show. Yes
And each record of your VCRs table describes an episode of a show.

Actually the vcr table describes ALL the episodes found on that one VCR tape.
There's about 19 tapes with 8-15 episodes per tape.
But it creates a record for each episode. ie: episode 1 on tape 1, 4, and 5. 3
entries in the VCR sub form for the same episode
For each VCR record, the title, and tape number change.

Please correct me if I am
wrong. In which case you will need to provide some examples of the data
so I can see what's going on. But otherwise, just make one table, which
combines the fields which you use from the two existing tables.

Actually, I Barney Googled it.
I have a sub form within a form that access created by using the 2 tables, and
fields from each table.

It fills in the episode number in the vcr list, but it'd also be nice if it
filled in the title also.

As for combining, I want the 2 separate tables. I'm using a form to fill in the
blanks for the VCR list. Maybe it might be simpler to just add a field to the
episode table for the vcr number, but that might not take into account that
there's sometimes copies of the same episode on more than one tape.
Where for some reason this form + sub form does let me enter the same episode
more than once.

Do you want me to send the tables ?, form or what ?
and where..

 

[Husky]

Actually the vcr table describes ALL the episodes found on that one VCR tape.
There's about 19 tapes with 8-15 episodes per tape.

Actually to more specific, the episodes table, is just a reference table.
Reference for the episode number, and title. I also have a text episode guide
open in a text window that gives me the description of each episode.
I find the episode by searching the text for specific words to describe each
episode ie: search term 'woodpecker' finds 'the wood, the bad, and the ugly'
episode. From that description I now have both the episode number title, and
description to enter into the VCR table.
with the episode table [reference only] and the vcr sub table form, I hit the
episode number in the GO TO box, then enter the description, title, and VCR
number into the VCRs sub table form. This updates the VCRs table.
I'm just saying that since I am sort of combining both the VCRs and episode
tables into one form, there should be a way when I enter the episode number
into the episode form GO TO box, it should be able to fill in the episode
number AND title automatically in the VCRs sub form.
But it only fills in the episode number. into the VCRs sub form.
episode number is the key [not auto number] in episodes table,
videocollectionID [auto number, irrelevant number] is the key in VCRs table.

 

[Steve Schapel]

Husky,

To be honest, I don't know how to use database software like Access
according to Barney Google. The only way I know how to use it reliably
is to carefully consider the nature of the information you want to
manage, and the relationships between the various elements of the
information, and design the database accordingly. It now appears that
you can have more than one copy of the same episode on different tapes.
That means there is a one-to-many relationship in your data between
Episodes and Recordings. I am not sure whether you need a separate
table for VCRs or not... this would only apply if you wanted to record
information specific to each VCR, which so far does not seem to be the
case - this would only involve relatively trivial stuff like Brand,
Length, CoverColor, etc. So, it seems to me that you need these tables:

Table: Episodes
Episode
Title
Air_Date
Prod
Season
Num
Notes

Table: Recordings
RecordingID
Episode
Tape_Number

 

[Husky]

Husky,

To be honest, I don't know how to use database software like Access
according to Barney Google. The only way I know how to use it reliably

Do a web search for Barney Google.

is to carefully consider the nature of the information you want to
manage, and the relationships between the various elements of the
information, and design the database accordingly. It now appears that
you can have more than one copy of the same episode on different tapes.
That means there is a one-to-many relationship in your data between
Episodes and Recordings. I am not sure whether you need a separate
table for VCRs or not... this would only apply if you wanted to record
information specific to each VCR, which so far does not seem to be the
case - this would only involve relatively trivial stuff like Brand,
Length, CoverColor, etc. So, it seems to me that you need these tables:

Table: Episodes
Episode
Title
Air_Date
Prod
Season
Num
Notes

Table: Recordings
RecordingID
Episode
Tape_Number

Well I'm going with what I have, I'm almost done anyway's. There were only
about 19 total VCR's to catalog. Little extra work copying the title, and
description. And I'll be done before I can figure out how to get it to do
exactly what I want, but with the 2000 Access encyclopedia on hand, I'll have
time to browse deeper and see if it can be done when I finish this cataloging..

 

[Steve Schapel]

Fair enough, Husky.

 

[Husky]

Fair enough, Husky.

Did you find out who Barney Google is or how it relates to my comment ?

Could have said Murphie'd [as in Murphy's law(s)] it. But that's more of a
pessimists view.

 

[Steve Schapel]

Husky,

I already knew what you meant. Did you understand what I meant? You
have come to a forum about building databases to ask the assistance of
those who are experienced in building databases. Let's say this is a
forum about flying helicopters and you don't know nuttin' about flying
helicopters and you come here to ask the advice of people who are
experienced in flying helicopters... and then you decide to try and take
off backwards into a 40mph northerly according to Barney Google. Well,
good luck.

 

[Husky]

Husky,

I already knew what you meant. Did you understand what I meant? You
have come to a forum about building databases to ask the assistance of
those who are experienced in building databases. Let's say this is a
forum about flying helicopters and you don't know nuttin' about flying
helicopters and you come here to ask the advice of people who are
experienced in flying helicopters... and then you decide to try and take
off backwards into a 40mph northerly according to Barney Google. Well,
good luck.

You didn't google Barney Google.

In long terms, to replace the Barney google reference, I was saying that
"somehow without trying I got the form to do almost what I wanted by pure
accidental dumb luck."
or
I Barney Googled the thing.

I used the page showing the different ways how to try what I'm doing. Didn't do
what I wanted.

IOW: there's information [title and episode number] on the episode guide table,
that I wanted on the VCR table once I finished the cataloging.

The form that came out filled in the episode number ONLY. And I suspect that
was by the relationship settings. I tried adding Title from episodes to
movie_title in vcrs as a relationship, but it seemed it could only handle a
single relationship line.

 

[Steve Schapel]

Husky,

I apologise that I don't seem to be able to get my meaning through to
you. At the risk of flogging an extremely dead horse...

1. No, I didn't google Barney Google, I didn't need to, I knew what you
were referring to all along.

2. My point is this: Do you want to continue on the basis of "somehow
without trying ... by pure accidental dumb luck", or do you want to do
it properly?

 

[Husky]

Husky,

I apologise that I don't seem to be able to get my meaning through to
you. At the risk of flogging an extremely dead horse...

1. No, I didn't google Barney Google, I didn't need to, I knew what you
were referring to all along.

2. My point is this: Do you want to continue on the basis of "somehow
without trying ... by pure accidental dumb luck", or do you want to do
it properly?

That's what I bought the access 2000 encyclopedia for. But it wasn't easy to
find the answer I was after. Which is why I asked here.

What I thought was the answer, only mentioned the words 'I forget now', but it
wasn't to be found anywhere in the chapter.

Yes I'd like to be able to take the contents of related fields in 1 table, and
automatically have them copied to related fields in a form I use to update a
2nd table.

in simplest terms.
table 1, field: title copy to table 2, field :VCR title when I enter the
episode number that defines the similar records.

table1 table2
tables episodes VCRs
_______________________________________
field1 episode episode
field2 title movie_title
field3 tape_number -->unique
field4 notes --unique

The current form has all the fields of table1, and a sub form of table2 with
all fields. When I entered the episode in the form for table1, it auto filled
in the episode number in the sub form. I had to copy the title from the top of
the form in table1, to the movie_title field in the sub form of table2.
I have since created a new table1, that ALSO includes the notes/description of
each episode.

FWIW: I've finished the cataloging. except for one tape that I haven't found
yet, so far. Last years hurricane scattered everything.

But I have found a lot of unlabeled tapes that I now need to label, so this
will be an ongoing project even if it is no longer about Home Improvement.

And what the hey, learning how to copy more than 1 field to an identical field
in a sub form, or secondary table automatically, simply by entering 1 of the
related fields should be a useful function.

As for reading this encyclopedia, There's 1300+ pages. It's going to take some
time. It's over two inches thick.