Automatically fill info when inputting data using a form

[Grant2012]

Firstly, I apologise if this simple issue is covered elsewhere. I have searched everywhere for this solution but can't find the answer.



I have a database which I use to create a Bill of materials, it has the following tables -

Customers (usual client data and a CustomerID, Automumber)
Masters (These are Compact Disc Audio masters. Each master has a unique identifier "CatRef" Then other info such as Title, date received etc...
Titles (This is actually a Bill of Materials which includes the associated Audio master(s) and has information regarding the the type of packaging etc..

So, when I receive a new master I want to add this using a form (FrmNewMaster). Q. What field do I need on my form to associate the master with a customer?

Then, when I have the title information (packing info etc) I want to use a form (FrmNewTitle) to create the new title. I want to be able to type in the Audio masters unique identifier "Catref" and have the form automatically update the Customer and Title Fields from the Customer and Masters tables.

If anyone could offer any suggestions on how to do this I would be very grateful indeed.

Grant

[recyan]

Till someone comes along,
are the below correct ( the question is the result of my ignorance about Compact Disc Audio ) ?
One Customer can buy many Masters.
One Master can have many Titles.
One Title is associated with one & only one Master.

If possible, can you post a couple of examples of Masters & Associated Titles.

Thanks

[Grant2012]

Hi Recyan
Thanks for responding. One customer can supply many masters. Think of a record label (Customer) having different artists putting out new albums.
One master can only be used on one title.
One title usually only has one master but can have several, think of a 2CD set, each CD is recorder from one master (CD1 & CD2).

I have attached screen shots of my 2 input forms to assist.

Thanks again for any help.

Grant



I

[June7]

Not quite understanding the Titles table. Is this essentially a customer order? You need fields in Titles for CustomerID and MasterID?

[Grant2012]

Not quite understanding the Titles table. Is this essentially a customer order? You need fields in Titles for CustomerID and MasterID?

Thanks for responding June7

The titles table is essentially a Billof Materials for a particular title, lets say "Bat out of hell by Meatloaf" The record company send me the audio master and I log the recept of this in the Masters table. It has crucial information like the Cat Reference "MEATLOAFCD001" and the title (above) and also who the record label is (Customer).

When I receive the actual order from the client it contains the packaging specification so I then add this data to the Title table, maybe it would be better called the BOM (Bill Of Material) table?

I know that I shouldn't be saving duplicate information in different tables but I don't know any other way to relate the Customer with the masters they own and then the titles that use those masters......

This is the design view of the Title table.



Thanks for your help.
Grant

[June7]

Use CustomerID in Titles table but don't store Customer (text field). Retrieve Customer info in query that joins tables.

Maybe need MasterID field in Titles. Really depends on answers to questions posed by recyan in post 2.

Exactly what are you keeping track of? You receive master recordings which you reproduce and package for commercial distribution?

[Grant2012]

Thanks June7
I think what I'm not understanding is the relationships. The Masters table is the first data I create for a new job as I receive the audio master first usually. So I add the Title name and Catalogue reference along with other info to the Masters table. This gives me a record of the receipt of that particular master.

Then once I have the packaging specification I add this info which sits in the Titles table. In my head I need to show who owns the title (Customer), what the title is (Title) and what the Catalogue reference is (Catalogue reference) when maybe I don't really need to store this information as long as the correct tables are related?

What I am keeping track of is precisely that...

Customers own titles and send me parts to manufacture those titles. The main parts are Masters which I need to keep track of, who owns the master, and what titles they are used in.

I then use various tables to add other information such as what type of print is used on a particular title and what type of case.
This allows me to build a job and store that information for future.

I just think I need help on how these tables should be related, I can then decide how best to use queries and forms to input the data <hopefully>

Grant

Use CustomerID in Titles table but don't store Customer (text field). Retrieve Customer info in query that joins tables.

Maybe need MasterID field in Titles. Really depends on answers to questions posed by recyan in post 2.

Exactly what are you keeping track of? You receive master recordings which you reproduce and package for commercial distribution?

[June7]

I don't understand relationships either.

Who is the customer - whoever sends you the master?

Who owns the master - the customer?

Who owns the titles - the same customer?

[Grant2012]

Yes, if you think of the customer as the record company and I am the manufacturer (customer supplier)


Who is the customer - whoever sends you the master? - Yes

Who owns the master - the customer? - yes

Who owns the titles - the same customer? - Yes

Grant

I don't understand relationships either.

Who is the customer - whoever sends you the master?

Who owns the master - the customer?

Who owns the titles - the same customer?

[June7]

Assuming every master can have only one customer and every title can have only one master, then:

tblMaster and tblCustomers have a link.

tblMaster and tblTitles have a link.

Customer info does not need to be in tblTitles. Customer and Title have an indirect link through Master record.