Help With Database and Join Table

[MGRLVR]

Hi! I have been lurking this group for awhile hoping to learn what I need
for my database. At this point I’m not even sure I know the right questions
to ask. I’ll explain what I would like to do as briefly as possible.

I currently have a database of operating carousels in North America.
http://www.nca-usa.org/NCAcensus.html I have been told it is a “flatâ€
database because all field are contained in one large table. I understand it
need broken up into smaller tables. I will try this at a later date.

I want to do a new database for defunct carousels. My information comes
mostly from photos, articles and other historical documents. Since a
carousel can be located in many parks and a park can have many carousels I
believe I need a one-to-many relationship for them. Next, and here is get
lost. In reading my books and posting for this group I believe I need a join
table to link carousels and parks together? I also need someway of adding
the source of my info (photos, articles, etc) with each record. Sometime I
will only know carousel and not a park or a park and not a carousel. I will
also want to keep data on each carousel and data on each park. I assume
separately in associated tables. When I query a park or carousel I wish to
know all parks a carousels has been at and/or all the carousels that operated
at a park and of course the reference.

I know this is lot to ask and I hope someone can help me out. I learn fast
but this more than I can figure out by myself. If anyone would like to help
I would certainly appreciate it. Please let me know what other info is
needed.

Patrick Wentzel
National Carousel Association Census Chairman and Director
Parkersburg, WV

 

[tina]

well, i'd start by suggesting that you have only one database, to store data
on both active AND defunct carousels. you can "flag" a carousel record as
defunct with something as simple as a Date/Time field, for the date the
carousel was "retired"; or, if there is more data to track when a carousel
is retired, you can use a child table - linked to the main carousels table -
to track that data.

beyond that, Patrick, i really, really recommend that you read up on
relational design principles. you're not likely to learn enough in these
newsgroups to get by without specifically studying relational design. once
you understand the basics, you'll be in a much better position to "know the
right questions to ask", and you'll have a framework of knowledge to help
you understand and apply the answers when you get them (like my comments in
the first paragraph above). for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Claudy]

Hello my name is claudy
i will trache by jullie in the groop

 

[MGRLVR]

Tina,

Hi! Thank you for replying. You may be correct in that I should combine he
two database. My problem is the active database has a large number of fields
that I use that will never be used for the defucnt database. Fields like
address, contacts, and much other data I will never use for the defunct
database. What I would like is a "simple" database to keep my references
(photos, etc.) and how they relate to the defunct carousels and parks. I'm
sure this is not as simple as I'm making it out. In most cases when I find a
photo or reference to a carousel or park the information will be limited.

I do need to do more studying about database design. I was hoping to learn
enough to at least make a join table for my database and get started. The
other problem I have had when trying to make this database is how to use a
form to enter data. If you can provide any help to get me pointed in the
right direction I would appreciate it very much.

Patrick

 

[tina]

I was hoping to learn

enough to at least make a join table for my database and get started.

that's kind of like a non-driver saying s/he wants to learn only a couple of
"rules of the road" before getting behind the wheel. not a good idea.
(though where i live there seem to be a lot of people who only learned one
or two. <g>) i've already pointed you in the right direction; it's up to you
whether you go that way or another way. either way, good luck with your
project.

hth

 

[MGRLVR]

Tina,

Thanks again for your reply. Actually I saw the link you sent me a couple
days ago searching for help with the join table. I have the Access Bible and
I've searched around but still don't understand how the join table works and
sub-forms for entering data. What I found it looks like a sub-form will be
need to enter data for the table linked with a join table?

I'll go back and reveiw the link you sent me. I believe for this project a
seperate database will be best knowing my data. Perhaps if I ask my question
another way it might more clear what I'm trying to do.

Any help understanding the join tables would be helpful.

Patrick

 

[MGRLVR]

Tina,

Hi! It is me again. I wasn't sure if I still had a database a programmer
friend of mine at work helped me with. I have it. Would it be possible to
send it to you or someone that would be willing to help me? I do wish to
learn as much as I can about Access but I learn by example, that is just me.

Anyway let me know if you are interested or if you know of someone who might
be able to help me.

Thanks so much.

Patrick

 

[James A. Fortune]

Hi! Thank you for replying. You may be correct in that I should combine he
two database. My problem is the active database has a large number of fields
that I use that will never be used for the defucnt database. Fields like
address, contacts, and much other data I will never use for the defunct
database. What I would like is a "simple" database to keep my references
(photos, etc.) and how they relate to the defunct carousels and parks. I'm
sure this is not as simple as I'm making it out. In most cases when I find a
photo or reference to a carousel or park the information will be limited.

I do need to do more studying about database design. I was hoping to learn
enough to at least make a join table for my database and get started. The
other problem I have had when trying to make this database is how to use a
form to enter data. If you can provide any help to get me pointed in the
right direction I would appreciate it very much.

If the defunct carousels have moved around , you'll need a
many-to-many relationship.

Here's an example schema with two many-to-many relationships
(DefunctCarousels.mdb):

tblParks
PID AutoNumber
ParkName Text
ParkCountry Text
ParkStateProvince Text
ParkCity Text
ParkZipCode Text
ParkData Text

tblDefunctCarousels
DCID AutoNumber
PID Long (foreign key)
CarouselName Text
GeneralCarouselData Text
CarouselDefunctDate Date

tblDefunctCarouselParks
DCPID AutoNumber
PID Long (foreign key)
DCID Long (foreign key)
CarouselStartServiceDate Date
CarouselEndServiceDate Date
ParkSpecificCarouselData Text

tblHorses
HID AutoNumber
DCID Long (foreign key)
HorseName Text
GeneralHorseData Text

tblDefunctCarouselHorses
DCHID AutoNumber
DCID Long (foreign key)
HID Long (foreign key)
HorseStartServiceDate Date
HorseEndServiceDate Date
CarouselSpecificHorseData Text

tblParkAttachments
PAID AutoNumber
PID Long (foreign key)
ParkAttachment Text

tblDefunctCarouselAttachments
DCAID AutoNumber
DCID Long (foreign key)
DefunctCarouselAttachment Text

tblHorseAttachments
HAID AutoNumber
HID Long (foreign key)
HorseAttachment Text

This allows you to track a specific horse among many carousels and to
track a specific carousel among many parks. If you don't need or want
to track individual horses then you can eliminate tblHorses,
tblDefunctCarouselHorses and tblHorseAttachments.

Input for this schema would include separate forms for parks, carousels,
horses, linking parks to carousels, linking carousels to horses and
three separate forms for linking attachments.

For attachments, I like to have an access form that allows a user to
select any file and have it copied to a directory structure on the hard
drive that mirrors the data, creating directories as needed. For
example, C:\DefaultCarousel\Attachments\Parks\<PID>\p1.pdf or
C:\DefaultCarousel\Attachments\Horses\<HID>\GrayLady.jpg. You can use
the directory structure alone to find all the attachments or store the
path of the attachment in one of the three optional attachments tables.

The extra fields are not much of an issue since you can just leave them
blank for defunct carousels. You might want to have an attachment for
an active carousel some day. If not, you have to decide whether or not
the existence of attachments (or addresses and contacts) is enough to
warrant a separate database.

Post back if you need help creating any queries using a schema similar
to what I've shown. The example is intended to get you to think about
how you're going to use your database. If you can come up with the
queries you need with a normalized schema, you've probably got the
correct starting schema. If not, keep the schema normalized and adjust
it until enables you to get the data you want. Also, try to anticipate
what new information you'll likely need once you have everything
working. Tina's idea of using a Y/N field to indicate whether carousel
is defunct or not is an example of that kind of foresight. It's up to
you to start with the best schema you can based on your best assessment
of current and future needs. When creating a new database I continually
refine the schema until I'm convinced that I can get all the data I need
for every report I think I'll need before creating a single table.
Sometimes I use the schema to create a few tables with a few records
each for the purpose of creating test queries so that I know that the
schema works. Extra time spent planning the schema more than pays for
itself.

Finally, consider hiring a local Access professional to help you get
started with this. Keep in mind that Access database files (.mdb) are
not a good repository for information to be displayed on web pages.

James A. Fortune
(e-mail address removed)

 

[MGRLVR]

James,

Thank you so much for your reply and the time you put into the examples.
The examples where great. Below is what I have been putting together. I
have so many questions.

Typical flow for entering a record would be a information source in this
case a photograph. The photo could be an identified park and identified
carousel or unknown for both. Depending on the amount of information I can
learn or know from the source I would add data to the tables as I could.
Since the carousel and the park are together this will be a unique combo. A
carousel can have been in many locations and a park can have had many
carousels. A park can also have more than one carousel at any given time.

Tracking the reference is very important. In my example of the photo, the
RefID will become the file name for the photo which will be scanned and
stored on my hard drive.

I’m having difficulty in understanding the Join Table which I have tried to
produce.

I want to be able to enter the data via a form. As I mentioned I was told
or read that I would need a sub form when using a join table?

I’m an Industrial Hygienist, carousel historian and a pretty fair
woodcarver. If I can help anyone out in these disciplines let me know. I’m
fascinated by databases and hope to learn more than enough to do my
historical defunct carousel database project.

I hope my table structure is presented okay.

Thanks so much for your help.

Patrick

Reference Table

RefID Auto number
RefType Text (photo, book, etc.)
RefDate Text (date for this ref)
RefDateEst Text (estimated date if unkn)
RefCreateDate Text (date ref was created in database)
RefMemo Memo


Carousel Table

CarId Auto number
NCAHDOCNum Text (number from old database)
ManfPltf Text (carousel manufacturer)
PltfID Text (SN or code number)
ManfFigs Text (animal manufacturer)
ManfDate Text (manufacturer date)
Rows Text (rows of animals)
PltfType Text (Jumping,stationary,track,etc.)
Menagerie Y/N (Y if other than horses)
Rebuilt Y/N (rebuilt from orig config)
Disposition Text (Operating, fire, auction, unk)
DispositionYR Text (final year of operation)
Memo Memo


Location Table

LocID Auto number
LocName Text (park or location name)
LocOwner Text (location owner)
LocCity Text (location city)
LocState Text (location state)
NearCity Text (nearest large city)
OpFrom Text (operation state date for this loc)
OpTo Text (operation end date for this loc)
OtherName Text (other names loc is known by)
Memo Memo


Carousel Location Junction Table

CarLocID Auto number
CarID Text (carousel ID for this unique combo)
LocID Text (location ID for this unique combo)
DateStart Text (start date for this unique combo)
DateEnd Text (end date for this unique combo)

 

[James A. Fortune]

James,

Thank you so much for your reply and the time you put into the examples.
The examples where great. Below is what I have been putting together. I
have so many questions.

Typical flow for entering a record would be a information source in this
case a photograph. The photo could be an identified park and identified
carousel or unknown for both. Depending on the amount of information I can
learn or know from the source I would add data to the tables as I could.
Since the carousel and the park are together this will be a unique combo. A
carousel can have been in many locations and a park can have had many
carousels. A park can also have more than one carousel at any given time.

Tracking the reference is very important. In my example of the photo, the
RefID will become the file name for the photo which will be scanned and
stored on my hard drive.

I’m having difficulty in understanding the Join Table which I have tried to
produce.

I want to be able to enter the data via a form. As I mentioned I was told
or read that I would need a sub form when using a join table?

I’m an Industrial Hygienist, carousel historian and a pretty fair
woodcarver. If I can help anyone out in these disciplines let me know. I’m
fascinated by databases and hope to learn more than enough to do my
historical defunct carousel database project.

I hope my table structure is presented okay.

Thanks so much for your help.

Patrick

Reference Table

RefID Auto number
RefType Text (photo, book, etc.)
RefDate Text (date for this ref)
RefDateEst Text (estimated date if unkn)
RefCreateDate Text (date ref was created in database)
RefMemo Memo


Carousel Table

CarId Auto number
NCAHDOCNum Text (number from old database)
ManfPltf Text (carousel manufacturer)
PltfID Text (SN or code number)
ManfFigs Text (animal manufacturer)
ManfDate Text (manufacturer date)
Rows Text (rows of animals)
PltfType Text (Jumping,stationary,track,etc.)
Menagerie Y/N (Y if other than horses)
Rebuilt Y/N (rebuilt from orig config)
Disposition Text (Operating, fire, auction, unk)
DispositionYR Text (final year of operation)
Memo Memo


Location Table

LocID Auto number
LocName Text (park or location name)
LocOwner Text (location owner)
LocCity Text (location city)
LocState Text (location state)
NearCity Text (nearest large city)
OpFrom Text (operation state date for this loc)
OpTo Text (operation end date for this loc)
OtherName Text (other names loc is known by)
Memo Memo


Carousel Location Junction Table

CarLocID Auto number
CarID Text (carousel ID for this unique combo)
LocID Text (location ID for this unique combo)
DateStart Text (start date for this unique combo)
DateEnd Text (end date for this unique combo)

Here's how I understand join tables. You may have heard that SQL is set
based. It turns out that it is actually the join fields and not the
data itself that responds/corresponds to set relationships. Suppose I
set up a many-to-many relationship between tblA and tblB:

tblA
AID AutoNumber (primary key)
MoreStuff SomeType

tblB
BID AutoNumber (primary key)
EvenMoreStuff AnotherType

tblJoinAB
JABID AutoNumber (primary key)
AID Long (foreign key)
BID Long (foreign key)

When examining the primary keys AID and BID, the records in tblJoinAB
form a subset (not necessarily a proper subset) of the cartesian set of
all values of AID crossed with all values of BID. Since all the AID,
BID combinations that actually exist are in the join table, the join
table is essential in obtaining existing relationships between tblA and
tblB. But the queries don't usually deal with only the key fields from
the two tables. Therefore, in most cases you need to perform a SQL join
or two with the join table in order to incorporate that fact.

You said:

Sometime I will only know carousel and not a park or a park and not a
carousel. I will also want to keep data on each carousel and data on
each park. I assume separately in associated tables. When I query a
park or carousel I wish to know all parks a carousels has been at and/or
all the carousels that operated at a park and of course the reference.

Here are a few (untested) examples of queries using your table structure:

1) Find all the parks where a particular carousel has been:

SELECT LocName FROM (Location INNER JOIN [Carousel Location Junction] ON
Location.LocID = [Carousel Location Junction].LocID) INNER JOIN Carousel
ON Carousel.CarID = [Carousel Location Junction].CarID WHERE PltfID =
"ZKDW195254";

The junction table along with Carousel was needed because PltfID is not
in the junction table. The inner join with Location was needed because
LocName is not in the junction table.

2) Find all the old database carousel numbers that have been in a given
park.

SELECT NCAHDOCNum FROM Carousel INNER JOIN [Carousel Location Junction]
ON Carousel.CarId = [Carousel Location Junction].CarID WHERE LocID =
Forms!cbxLocation.Value;

The WHERE condition does not require the junction table name before
CarID because CarID is in only one of the two tables. The LocID has
come from a combobox on a form that shows the names of the parks but is
bound to the LocID. LocID is in the junction table so a second join to
Location is not required.

You're always going to have to use the join table along with one or both
of the other tables whenever the information involves both carousels and
parks.

Finally, you need to have RefID as a foreign key in perhaps Carousel if
you want to be able to connect the information with a particular
carousel. Note that what I posted before allowed a little more
flexibility. Then you can join Carousel using the mutual field RefID to
get a list of all the information attached to a particular carousel.

James A. Fortune
(e-mail address removed)