The Order Form Dilemma

Dear Deidrie,

I have a bit of a dilemma with my ordering system. I try and I try but it just won't work.

Can you help me get it all fixed and lovely?

Worried of Saxmundham

Hi Worried,

No problem you, we'll have you sorted in a jiffy.

To start with, this is how your table might want to look:

Notice that there's only one set of information in the tblOrderLine. I've cut off the screenshot to make it more visible - there's a lookup table over on the right for those of you not familiar with the admittedly unusual example Worried has chosen here.

You'll notice as well that the referential integrity has been enforced. I always think you can never have too much referential integrity.

What might not be obvious at first glance is that there's no Primary Key in tblOrderLine...

Once you have your tables sorted it's plain sailing. The juice covered textbook has information on or about page 250 which might come in jolly handy.

It will involve setting up a query a bit like this one.

Notice there's more than one table involved there. There's also a calculated field at the end for the cost: Cost:[Price]*[Quantity] or something similar.

Oh, a tip: try sorting the field on Order Number. You might just find it saves a bit of head scratching later on.

Then you can set up an Order Form a bit like this one.

You need to get the Form Wizard to wave his magic wand on this one as you're taking stuff from three places: tblCustomer, tblOrder and qryOrderedProduct. Fun - slightly confusing fun the first time you do it, but in my experience most of the best things are.

You'll notice I've used some of the Cool Formy Things, like a Calendar control and a spinner, to increase the usability of the form. I've also taken a bit of time to get the layout as clear and usable as I can. I'm like that. Some people would say obsessive, others might just say odd.

So, Worried of Saxmundham, hopefully that might help. If it doesn't then I can recommend a nice crisp Chablis if that helps?

Best wishes,

Deidrie xxx