Confusion over adding records from Forms.

[dcb]

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.

Maybe there is a lesson on its way... The only thing I can think of is that there are going to be x number of set contract types and thats why you are linking them - this would be a n:n
But then I dont get the two PK's in the joining table?

Using that would normally be set up as
tblCustomerContracts
===========
CCID(PK)
CUstomerID(FK)
ContractID(FK)
--PhoneID(FK) No Duplicates ???? This can be put here now and can become a possible PK dropping CCID which would no longer be required

 

[dcb]

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.

Yes no - I dont really agree with anything.... It is a lesson and may be that the object is method 1/12 ???

The truth is it's quite simple in real life

Customer 1 : n Contract 1 : n Phones

Now where the great bit comes in !!!
STOCK???
Thats a whole new discussion......

 

[UponFrail]

There is no duplication in the PhoneTBL, there is 8 phones to choose from, and 12 contracts, to choose from.

 

[dcb]

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

I gotta love it:
Stock Level in a table.... calculated fields????
Ummm.... I can only assume that this is going to be the basis of a normalization lesson (I pray)

 

[UponFrail]

The stock level, is when a new order is made, it subtracts one from the stock level. This must also be functional.

 

[UponFrail]

The Database, is obviously about a company which sells mobile phones. Offers 8 Range of phones, and has 12 different type of contract, with existing customer who have many contracts.

Then in the PhoneTBL, there is the addition of stock_level and stock_reorderlevel. So, the functions should be, add a contract, to a new or existing customer, then that subtracts the record from the stock level, and therefore updates all the table. The structure is what i can't create obviously. Just seems complicated.

 

[dcb]

There is no duplication in the PhoneTBL, there is 8 phones to choose from, and 12 contracts, to choose from.

And multiple Phones are related to 1 Contract?

IE: Customer 1:n CustContract n:1 Contract 1:n PhoneContract n:1 Phone

If we go back to your original post re form

Customer form as per normal (you can add delete etc)

CustContract form should have:
CCID(PK) text box
CustomerID text box
ContractID - Combo Box populated by a query from tblContract

For ease make this a cont form as it only has one user defined field! - it gets customerID from the main form and CCID is self generated

The wizard will take you through the links when you insert the form - the relationship is CustomerID=CustomerID

 

[UponFrail]

So for each contract, with there is 1 phone. However customer can have many contracts or many phones. I'll try what you said. Thank you.

I think i'll have to read up more on my access, because this is still confusing me, even though i'm studying A2 Level IT.

 

[dcb]

So for each contract, with there is 1 phone. However customer can have many contracts or many phones. I'll try what you said. Thank you.

Give it a try

I think i'll have to read up more on my access, because this is still confusing me, even though i'm studying A2 Level IT.

Access has 3 facets:
Tables and Querys
Forms or UI
Reports

The first thing you want to get right is the tables and querys - as you dont have a copy of access at home download Postgresql (my fav) or MySql and practice on that... You will learn so much as there is no drag and drop etc - though GUI's are available - It will really teach you to think things out!

 

[UponFrail]

Yeah, tables, and queries, i understand well. It's the forms, and taking the data from my MakeTableQueries, and being able to, use forms to add data etc.

You said CCID(PK), CCID, has 2 primary keys, customerID, and contractID.

So say for example, i open up a form, i import the data from the tables, Customer and Contract, seting the contractTBL, as my subform. Would that be right so far?

 

[dcb]

Yeah, tables, and queries, i understand well. It's the forms, and taking the data from my MakeTableQueries, and being able to, use forms to add data etc.

You said CCID(PK), CCID, has 2 primary keys, customerID, and contractID.

So say for example, i open up a form, i import the data from the tables, Customer and Contract, seting the contractTBL, as my subform. Would that be right so far?

OK hang on - where did the make table queries come from?
No
CCID is the only PK - CustomerID and ContractID cant be PK's as you would then only be able to enter one of each

Form Customer's data source is tblCustomer - no Import
Form CustomerContract data source is tblCustomerContract - no import

You do not Touch your Contract Table except as a lookup via the combo box

 

[UponFrail]

MakeTableQueries, were required to minimize duplication. The imported data was just 2 massive tables. Then i did 4 queries, grouped them. Set PK's, and made relationships.

The original files has lots of duplication. We are required to make a valid structure with queries.

Then use these new tables, to put into forms to do what i described before. There is only meant to be 1 form, that searches adds contracts, for new and existing customers.

 

[dcb]

MakeTableQueries, were required to minimize duplication. The imported data was just 2 massive tables. Then i did 4 queries, grouped them. Set PK's, and made relationships.

The original files has lots of duplication. We are required to make a valid structure with queries.

Then use these new tables, to put into forms to do what i described before. There is only meant to be 1 form, that searches adds contracts, for new and existing customers.

that makes sense....

 

[UponFrail]

Okay my teacher explained the tables to me.

CustomerTBL, is 1
ContractTBL is 1
PhoneTBL is 1

Therefore the CustomerContractTBL, is the many, which links the tables.

So there is 1 customer, with many contracts, and 1 contract, with many phones.

That is the whole purpose of that CustomerContractTBL. All tables were produced using MakeTableQueries.

Also i am able to get a copy of Access 2007, is there any similar databases out there? Just to help me out, and help me learn Thanks guys.
Now the MAJOR problem: What is logical to me is, you set up a form, input the, customerTBL, and the contractTBL, set the contractTBL, as the subform, so you can scroll through the different customers, and see the many contracts they have.

However, since the contractTBL is a subform, i can't add contracts, which is the whole purpose of the form.

The purpose is to be able to add contracts, for Existing Customers, and for New Customers. That is the major stumbling block.

The teacher reassured me the structure is correct, although some may disagree, i'm really caught in confusion here.