Confusion over adding records from Forms.

UponFrail

Registered User.
Local time
Today, 20:29
Joined
Jan 26, 2010
Messages
19
Hey everyone! I'm currently studying access 2003 at school. I've came across a major stumbling block, in the current database we're studying, everyone is confused as well as the teacher, which is concerning.

We are given data, about customers, phone makes, and contracts. I've made 4 tables, CustomerTBL, ContractTBL, CustomerContractTBL, and PhoneTBL.

Primary Keys are: CustomerID, ContractID, and PhoneID.

The problem is, we're meant to create a form, and the purpose of this form, is to add contracts for new customers, and existing customers.

I'm confused how i go about this, so is my teacher, which again is concerning. Any suggestions?

Thanks in advance for any replies.
 
Check out the sample NorthWind database that comes with Access. Your contract will be similar to the Order/Invoice in the Northwind example.
 
Sadly, i don't have access, to that. See i only have Access 2003 in school.
My idea, was to create to forms, with subforms, for existing customers, and new customers. With contract, being the subform. But i'd need to add contracts as well, as customers. But i won't be able to do that with contract being the subform.

Therefore i'm stuck here. The idea is to, have a form, which allows you to add, contracts, for new customers, and existing customers. Which i have no clue, to do, because my original idea is flawed.

Any suggestions?
 
Thank you for the links. First and Third appear to be broke. The second one helped, but i'm looking to add records (contracts) to both existing and new customers.
 
I'll explain it all. You're giving data, 2 text files. Customer and Contract. I've designed the structure, which i've described above. Done all my queries, which run fine, minimize duplication, everythings running smoothly. Form, is the problem, because, i need to be able to add, records (contracts for phones), to the new customers and also existing. The form MUST be able to do this. Which is major stumbling block. Once the structure is created, you are then required to test it, by adding contracts for, new and existing customers.

My main problem is my structure of the forum, because i don't know how to go about it, my attempt has failed and is flawed. Now i'm stuck, and struggling to find the correct way to set up this form, which allows the required functionalility.
 
I think Link one will give you a good idea:
Dont know why you not getting it....
 
I'll explain it all. You're giving data, 2 text files. Customer and Contract. I've designed the structure, which i've described above. Done all my queries, which run fine, minimize duplication, everythings running smoothly. Form, is the problem, because, i need to be able to add, records (contracts for phones), to the new customers and also existing. The form MUST be able to do this. Which is major stumbling block. Once the structure is created, you are then required to test it, by adding contracts for, new and existing customers.

You dont have the db with you? can you post it?
I will give you a little example then which you can run in AccessRuntime
 
I think i've worked this out. The contract has many different options, such as duration, phone type, type of contract etc. So in the subform, to add the contracts, i'll need ComboBoxs? And macro's to search for new customers, and add new customers? Also, there is contract start date, how will add this, because i won't be able to add a combobox for that?

I know i may sound abit, unintelligent here, but i'm not 100% used to Access, but i'm getting there (:.
 
I think i've worked this out. The contract has many different options, such as duration, phone type, type of contract etc. So in the subform, to add the contracts, i'll need ComboBoxs? And macro's to search for new customers, and add new customers? Also, there is contract start date, how will add this, because i won't be able to add a combobox for that?

I know i may sound abit, unintelligent here, but i'm not 100% used to Access, but i'm getting there (:.

Is it a new contract for each customer?
 
DCB, i've got all my files at school. However my friend, has a copy of the database, this is a slightly different database, because the primary key has a different name, and there is one extra field, but he will post it up as soon as possible, again thanks for your help (:
 
Basically yes. The concept is that, you lookup existing customers, and view their current contracts. But also be able to add new contracts, as well as adding new customers, and being able to add new contracts for them as well.
 
Basically yes. The concept is that, you lookup existing customers, and few their current contracts. But also be able to add new contracts, as well as adding new customers, and being able to add new contracts for them as well.

OK... But then you dont have a n:n relationship!

tblCustomers 1:n tblContracts

This would be a simple subform in the customer form!
 
There is 4 tables. Customer TBL, CustomerContractTBL, ContractTBL, and PhoneTBL

The relationships are;

CustomerID -> CustomerID (CustomerTBL -> CustomerContractTBL)
ContractID -> ContractID (CustomerContractTBL -> ContractTBL)
PhoneID -> PhoneID (ContractTBL -> PhoneTBL)

Note that, PhoneID, is giving in both the Customer and Contract text files.

The CustomerContractTBL, consists of ONLY, CustomerID and ContractID, both are set as Primary Keys.
 
My teacher drew up the structure and designed. She said something about the table, making it one to many, instead of many to many.
 
Logically
1 Cust can have many contracts
1 Customer can have many numbers
Numbers may be related to contract

tblCust
======
CustomerID(PK)
PhoneID(FK)

tblContract
========
ContractID(PK)
CustomerID(FK)
PhoneID(FK)

tblPhone
========
PhoneID(PK)

Would you agree?
 
Yeah i agree with that. But my teacher said there should be 4 tables including, with the CustomerContractTBL, which now seems like a pointless table.
 
Logically
1 Cust can have many contracts
1 Customer can have many numbers
Numbers may be related to contract

tblCust
======
CustomerID(PK)
PhoneID(FK)

tblContract
========
ContractID(PK)
CustomerID(FK)
PhoneID(FK)

tblPhone
========
PhoneID(PK)

Would you agree?

Not so sure about the PhoneID in tblContract. I think the contract here that is dealt with is to do with a phone which comes with a number. From the contract you can get the number. Without a contract a customer does not get a number.
 
The PhoneTBL includes: Make of Phone, Bluetooth, MP3, Stock Level, Stock Reorder Level, PhoneID

ContractTBL: Duration, Type of Contract, Start Date Of Contract, ContractID, etc
 

Users who are viewing this thread

Back
Top Bottom