Just don't get it-linking data that is

[stacymae]

Hello,

I tried searching these pages in hopes that someone else was having the same
problems as I was but even the new users are more advanced than me.

I am trying to create a database for a small commercial imagery site that
will allow us to track our collection progress.

TblCollection
Collection ID- Auto Number [primary key]
Date of Pass
Satellite
Start of Pass
End of Pass
Total Scenes

Here's where my problems start, I need to track the number of scenes cut to
CD from each pass but there could be anywhere from 1-100 of them so I created
another table

tblScenes cut
CD ID [primary key]
Image Type
% CC
Satisfied

Now, how do I make a form/relationship/anything that will help me enter the
data from the pass, to include multiple CD's cut and later query by date and
find out how many scenes were cut on which pass?

I have many more questions but I don't want to chase everyone away right off
the bat.

Today I tried adding the CD ID to the tblCollection and then creating a
one-to-many relationship but when I created a form and subform to enter all
the data, it didn't connect to each other.

any help would be greatly appreciated
Thanks,
Stacy

 

[Marc]

Hi
IMHO, get the objects and their relationships defined first. Not sure if
I've understood it all but based on what you've said:
You have access to (a) satellite(s).
They/it make passes, and film continuously/in batches.
Each pass contains scenes.

You publish CDs of scenes.
There's more there but can't quite get a handle on it.

tblSatellite
SatId - autonumber PK
.... other infor

tblCollection

Collection ID- Auto Number [primary key]
Date of Pass SatelliteId - foreign key
Start of Pass
End of Pass

tblCD
CDId - autonumber PK
DateCut
... other identifying info
tblScene
SceneId - autonumber PK
CollectionId - foreign key
CDId - foreign key

then you use queries to calculate how many scenes are from each collection.

Your form has tblCollection as the source, with tblScene as the source of a
subform. tblSatellite is a cbo box on tblCollection. tblCD is either a
cbobox or another subform depending on what you need

HTH
Marc

Hello,

I tried searching these pages in hopes that someone else was having the
same
problems as I was but even the new users are more advanced than me.

I am trying to create a database for a small commercial imagery site that
will allow us to track our collection progress.

TblCollection
Collection ID- Auto Number [primary key]
Date of Pass
Satellite
Start of Pass
End of Pass
Total Scenes

Here's where my problems start, I need to track the number of scenes cut
to
CD from each pass but there could be anywhere from 1-100 of them so I
created
another table

tblScenes cut
CD ID [primary key]
Image Type
% CC
Satisfied

Now, how do I make a form/relationship/anything that will help me enter
the
data from the pass, to include multiple CD's cut and later query by date
and
find out how many scenes were cut on which pass?

I have many more questions but I don't want to chase everyone away right
off
the bat.

Today I tried adding the CD ID to the tblCollection and then creating a
one-to-many relationship but when I created a form and subform to enter
all
the data, it didn't connect to each other.

any help would be greatly appreciated
Thanks,
Stacy

 

[Bruce]

Try to describe your tables with a single sentence,
avoiding the use of "and" in the description. If you
can't, take another look at your table structure. Give
each table a primary key (unique identifier posessed by
only one record (row) in the table). You might need it
later.
Next, describe the relationships along the lines of:
Each Pass may have many scenes.
Each CD may contain many scenes.
Each CD may contain scenes from many different passes.
And so forth. This suggests that you need a table for
passes (tblPass), for CDs (tblCD), for scenes, and so
forth. If each pass contains many scenes, the primary key
from tblPass is a foreign key in tblScene. If the primary
key is autonumber (which is a good choice), the foreign
keys datatype would be Number (not autonumber). I'm not
sure how collection fits into this, or what you are
collecting.
For those of us not in your line of business, some
explanation of Collection, Pass, etc. would probably help
the rest of us understand what you need to do.
Linking means that if you enter the information needed in
tblPass, all of the scenes from that pass can be linked to
the pass by the primary key (PK)/foreign key (FK)
relationship described earlier. In Access 2000 or later
you will see a plus sign in tblPass. Clicking it will
open the linked table (tblScene). You can enter all of
the scene information right there for now. Once all of
the relationships are set up you can use forms and
subforms, which are often more convenient.
To establish relationships, click Tools > Relationships.
Open the two tables (tblPass and tblScene). Drag the PK
from tblPass (call it PassID) onto the FK (also called
PassID) in tblScene, and let go. A connecting line should
appear with 0 at tblPass and infinity at tblScene. Close
relationships, open tblPass, and take a look.
Remember that you need to define the PK field in table
design view, but the FK field is defined by being included
in a relationship. Start with two tables as I have
described (choose different tables if you prefer).
If the scenes have already been entered into tblScene, do
not attempt to copy them to another table if they are
moved to a CD. Instead, use the PK from tblScene as the
FK in tblCD. If you are storing the same information in
two places you are doing something wrong. Store data
once, and link to it as needed. I can't overstate the
importance of this. Having said that, consider the case
of somebody's address. If you need a record of an old
transaction to include the address at that time, you need
to store the actual address in the transaction table, or
in a linked table. If on the other hand you need an
employee's payroll history to show up even if their name
has changed, you must link to the EmployeeID field.
Use queries to arrange your data (for instance, to sort it
by date or to specify if you want to limit what you see
only to data that has had a checkbox (a Yes/No field)
ticked). Later, use forms to manipulate data on the
screen, and reports to print it.

-----Original Message-----
Hello,

I tried searching these pages in hopes that someone else was having the same
problems as I was but even the new users are more advanced than me.

I am trying to create a database for a small commercial imagery site that
will allow us to track our collection progress.

TblCollection
Collection ID- Auto Number [primary key]
Date of Pass
Satellite
Start of Pass
End of Pass
Total Scenes

Here's where my problems start, I need to track the number of scenes cut to
CD from each pass but there could be anywhere from 1-100 of them so I created
another table

tblScenes cut
CD ID [primary key]
Image Type
% CC
Satisfied

Now, how do I make a form/relationship/anything that will help me enter the
data from the pass, to include multiple CD's cut and later query by date and
find out how many scenes were cut on which pass?

I have many more questions but I don't want to chase everyone away right off
the bat.

Today I tried adding the CD ID to the tblCollection and then creating a
one-to-many relationship but when I created a form and subform to enter all
the data, it didn't connect to each other.

any help would be greatly appreciated
Thanks,
Stacy

.