Solved: I am sooo confused & desperate--Access 2007

[featherhorse]

Hello. I am trying to build a database. I had a class on Access 2003 almost 2 years ago and when I replaced my computer, it has Access 2007 and I find they are totally different and I have read tutorials, helps, etc. until I am so confused, I don't know what I am doing. I attend a local community college and cannot get anyone there to even help. I know once I get started, I will probably be ok, but I cannot get my table normalization/relationships to work. I understand the one-to-many, many-to-many, etc. and I know what primary keys are and foreign keys are, I just cannot bring it all together to work. My husband has a small home based business and I was using it to set up a database for one of my classes
(Systems Analysis and Design). I am getting behind in my class because I have been trying Hfor 3 weeks now to get this database up and I cannot move on with my project until I am
done with it. I am running out of time and desperately need some help, please. There are so many variables to his business. I'm not sure what information you need about how the business works, but here are the tables I have set up:

Licensee Table

Company Name (primary key)
First contact
First contacts title
Second contact
Second contacts title
Department
Office Phone
Cell Phone
Fax Number
Email address
Address
Address 2
City
State
Region
Zip/postal code
Country
Product type

Contract Table

Contract Number (primary key)
Company Name (foreign key)
Beginning date
Ending date
Royalties %
Flat fee
Adv against Royalties
Image Name (foreign key)
Product type
Notes

Follow-up Table

Company Name (primary key)
Image Name (foreign key)
Portfolio Number (foreign key)
Follow-up date

Images Table

Image Name (primary key)
Category
Size
Original Artwork Price

Portfolio Table

Portfolio Number (primary key)
Portfolio Type
Company (foreign key)
Image Name (foreign key)
Date Sent

Hi-Res CD Table

Hi-Res CD Number (primary key)
Contract Number (foreign key)
Company (foreign key)
Image Name (foreign key)
Date Sent


Thank you soooooooo much!!!!

 

[featherhorse]

This is an online class and my instructor told me to find help were ever I could, even another campus if they would help because she doesn't know a thing about databases. I have tried. Just thought I would let you know so you wouldn't think I was cheating or something in my class. This class is about building a system, not databases.

 

[OBP]

featherhorse, I would strongly suggest that you do not use "Names" as Key fields as Access then has to store "Text" which takes up more memory and disc space than numbers.
I would use an ID Key field for each Table using an Autonumber field.
It is not possible to advise on how to set up the Relationships as you need to explain how the database is intended to work. ie. Which is the Primary Table with the data that comes first, then how do the other tables relate to it.
Do you start with the Company and their Contact and then select some "Images" or "Portfolios" to send them or that they own?
What is the intention of the "Follow Up" table.

I think that you need your Husband's input here, does he currently have a database?
Would he use this if you got it working?
What and how does he currently control his data?

 

[featherhorse]

Thank you so much for getting with me. The reason I used names was because the database isn't going to be extremely big and space isn't an issue. We have been doing this for over 25 years and the database might only consist of a hundred or so companies. We deal a lot with the same companies. The company names are unique and there aren't any duplication of names. The images themselves are also recognized by their unique names and never duplicated. For example: Visions, Whispering Winds, Tigers in the Mist, etc. My hasband doesn't have a database, it would be used if put into place and right now he writes things on little pieces of paper, sticky notes, plain typing paper and sticks them in folders. It takes forever to find out any thing like when contracts expire, or what images are available for use, etc.

Ok, the business works like this: Contact is made by phone or email either my husband contact them or they contact him. I will try to give examples of different senioros.

Different companies can have the same products or they can have unique products. For example: some might have puzzels, mugs, note paper, etc. Others might have tables with etching in glass for table top, There might be shower curtains, bedspreads, towels, etc. One company that makes puzzels, might use one image/images and another puzzels company may want to use a totally different image/images. When images are under a licensing contract, that image/images aren't available for use to another company until the contract runs out. This is where being able to keep track of contracts comes in to play.

After contact is made with a company:

If they want to use an image/images:
negocialte what image or images will be used on what product/products
payment type (royalties, flat fee, or advancement against royalties)
discuss the details of the contract (beginning date, ending date, etc.)
Request a hi-res CD
Some companies only required one contract to cover several images and some companies
require a contract for each individual image they use.

If interested but not ready to make a decision at that time or he is told they aren't taking on new stuff at this time but check back at a later time:
Some will request a portfoilo CD, or hard copies, or email ( which a follow-up might be required)
Some may say they aren't looking right now, but to get back with them at a later date (follow-up.
We will need to know what images they are or would be interested in for what products to follow-up with them.
The date a portfolio is sent is used to help determine a follow-up date.

That's kinda of it.

Some of the information under the tables, for example, the images table, I want to be able to run reports to keep track of what images are available in what catagory (wildlife, bears, big cats, people/portraits, farm scenes, etc.) And the different sizes and price of each original piece of artwork.

I hope this helps and makes sense. Just let me know what additional information you might need. I do have my table already in Access 2007 with some data added if it would help to send it for you to look at.

Thank you very much, you don't know how important it is for me to get this straightened out. You are saving my life! I am also open for any suggestions that might work better.

 

[OBP]

featherhorse, I have sent you my email address via Private mail.

 

[slurpee55]

Hey, OBP, if you can, post her DB here - I am starting to get back into the Forum (after being gone for some time, except for sporadic visits) due to work considerations. I'm not out of the woods yet, but nearly so. And I miss the camaraderie here.

 

[OBP]

slurpee, if it is OK with her, I will.
I thought you had been a bit quiet lately

ps Who is this Poster "camaraderie" then?

 

[slurpee55]

Featherhorse, given that you have 2007, initially please just view this in the format I have uploaded it in - don't convert it. I only have 2003, and can't deal with the new Access version. This is rather tossed together, but might give you a start.

 

[slurpee55]

Hey Mr. T! Good to see you - we've been swamped....I managed to get out of work Friday, but only by having a Grand Mal seizure....

 

[featherhorse]

Thank you!! It is ok with me to post my DB for slurpee55 to see. I have sent you an email with my db attached.

 

[featherhorse]

I redone my tables and so I have sent you my revised database, ignore the first email.

 

[OBP]

featherhorse, I have emailed you some questions.
Yes of course you can post your Zipped database for slurpee to see.

 

[featherhorse]

Here is my revised database so for slurpee to see. Let me know what other information you might need. Thank you very much!!!

I hope I done this attached right.

 

[slurpee55]

featherhorse, a lot of people don't have the ability to open .rar files, so here is the zipped version of your file

 

[featherhorse]

OBP, did you get my email answering the questions that you had for me?

Thank you slurpee55.