Relationships

[Tech]

Hi there. I am new to relationships. and again, I need help, and hope that you can help

I have a database with different forms/tables etc...

take a look, keep downloading regularly as I update it:

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

Right, There, I think, must be a relationship between all the tables, like for example:

the customer details (like customer number, name, address, city, country, postcode) are put in the order form.

There have been text boxes and a combo box been put in the order form, take a peep, i think u will know what I mean.

Now, how can I create some sort of relationship? For example, a customer number is in the customer details and the order form, that is a relationship, right? but in the order form, I did NOT make a customer number field, or any of the customer details field in the order table, would this be ok?

Technically what is a relationship used for? I am confused

 

[Jack Cowley]

First, search Help for Relationships and then click on then 'Work with relationships' to find out what they are about.

Second, make your Primary key (CustomerID) an Autonumber and not the text that you are currently using.

Next, look the Northwinds database and see how they have joined their tables.

You do not need the tables 'Unpaidorders' and 'CustomerOrderHistory'.

Use standard naming conventions for your tables, queries, forms, etc. For example your CustomerDetails table should be named 'tblCustomerDetails' and your Customer Details Form should be named 'frmCustomerDetails'. Do not put any spaces in table, form, report, query or field names.

Aren't you glad you asked?

hth,
Jack

 

[Tech]

thank-you

can someone please attach the northwind database, it's not on my PC for some reason and cannot b bothered to put in the CD cause it's late here and i am sooo tired at this database!

 

[Jack Cowley]

You can download Northwind here.

hth,
Jack

 

[Tech]

ty

i'm going to fail this course

urgh

right, what should I do? I mean, look at my table, all muddled up

What I WANT, in my order form is that, a customer number and a product number is chosen from the drop down box and all appropriate info is shown - great (thanks bj)

but, IF i enter a new record, what ever the last customer details where, is shown in ALL the records, wether u change them or not, still the same

And these text boxes/combo boxes were made manually, wrong idea? How do I make the relationship work? and I have followed the nw db, seems like whatever link u want to happen, the recieving end MUST have that field (wherever possible), but my order form doesn't have much........well, none for the customer details and product details what shall I do?

 

[Jack Cowley]

A levels? Did you wait until the last minute as all students do? Just an observation, not a criticism!

If this is due tomorrow you are in trouble as I am sure you are exhausted and getting no where fast. It is hard to tell you what to do but you probably do not have your forms set up correctly. The Orders form should be a subform with your Customer table providing the data for the main form. This way you select a customer and then add order data in the subform. I have not looked at your db so maybe that is what you have.

My suggestion is to hang it up, get some rest and get cracking tomorrow...

Good luck with your project and don't give up the ship!

Jack

 

[Tech]

hehe, i ain't like all the other students

it's for uni...and they haven't really taught much on this subject

it's not due in until March 7th (or further than that) so there is time but need to get it done and out the way!

OK, this is WHAT I actually want:

in the orders form, a customer form (or something) that will let us enter a customer number, if it exists, show all the appropriate details of that customer (kinda done, but manually using text boxes etc...)

the same thing again, for product details, on the order form.

But, with this product details, I would like it like the order form in the nw db, datasheet view with drop down box...that is PERFECT. how can it be achieved?

I would also like that annoying problem to go away where if we enter a customer number, it is repeated throughout all the records!

ty, good night - 2morrow afternoon, I will try to crack on

 

[Jack Cowley]

My faith has been restored in the uni student! Glad to hear that you are 'ahead' of schedule!

You can create a combo box on the cutomer form using the Wizard that will take you to the record you want. Create a combo box on your Customer form and on the first screen select the 3rd item, "Find a record....". Finish the Wizard. Now when you select a customer from the combo box that customers record will be shown on the form.

As for how the forms work in the Northwind database I can only tell you that you will have to try and suss it out as best you can. If you open the form "Orders Subform" you will see how they did the combo box....

Good luck!

Jack

 

[Tech]

ty

It's actually, genrous of BJackson to make it work, great guy IT works..but whatever u said, it has been written in my own personal notes too

i just went out, bought 2 books, 1 of them being the dummies one...hope it helps...off for reading

tyvm again!

 

[Jack Cowley]

Well done!

Keep it up and when you get a chance say hello to my friends in Gorebridge....

Jack