Using normalised tables to create the form I want

[freda42]

I don't know if anyone can help me here, as it might be more complicated than I can explain easily! I started creating a database, and created one large table and associated form. The showed customer details at the top, with tabs for enquiry, quote, order and job details associated with that enquiry for that customer at the bottom.

I was then told about normalising data, so I am now trying to simulate the form's layout with my new table structure, which comprises a table each for customer, enquiry, quote, order and job data. I have set up a one to many relationship between the customer and enquiry, and 1:1 relationships between the others.

I can't get the form to display all of the customer, quote, order and job information associated with one enquiry. Whenever I try, I get the customer data showing, but nothing for the enquiry, quote etc data.

If someone can tell me how to post pictures I can show you what I want and what I have!

Thanks in advance...

 

[KenHigg]

Sounds like you need to read up on subforms?

kh

 

[KeithWilliams]

Hi,

I am puzzled why you created any 1:1 relationships. I would have expected the Customer table to have 1:Many relationships with each of the other tables (e.g. there could be zero or more Orders for a Customer).

I think you will need to create subforms for the data on the other tabs, and link the subforms to the main form. It sounds from your post like you have not done this.

Hope that helps,
Keith.

 

[Oldsoftboss]

Try using subforms for the related data. If you use a query as the record source at times no record will be displayed if a related table has no related data.

Dave

 

[freda42]

Hi,

I am puzzled why you created any 1:1 relationships. I would have expected the Customer table to have 1:Many relationships with each of the other tables (e.g. there could be zero or more Orders for a Customer).

I think you will need to create subforms for the data on the other tabs, and link the subforms to the main form. It sounds from your post like you have not done this.

Hope that helps,
Keith.

Thanks - I see the logic with the 1:many relationships and I'll try it.

I have been using subforms within my main form, but with little success as yet. I will keep trying.....!

 

[freda42]

Phew, I've got somewhere at last. Now I have subforms set up in the main form (whose data source is the enquiry data).

One more question - can I get the sub forms to display data in the same way that the main form does (in boxes with labels), as opposed to in tables?

Thanks again - often all I need is a gentle push in the right direction....!

 

[KeithWilliams]

Hi,

When you say "in tables" do you mean in datasheet view? You will probably want to change the form to Continuous Forms (in the Default View property). Then you can format the record how you want it. You probably don't want Single Form because there may be many records in the subform for one record in the main form.

Please clarify your problem further if this does not make sense.

Keith.

 

[KenHigg]

You can do a new form and use it as the source object in the sub form control on the main form. (Source object is a property on the sub form control).

kh

(Make sure to set the subfom to display 'continous forms' and not 'datasheet view')

 

[freda42]

Yes, thats what I meant - sorry, the terminology isn't quite automatic yet!

I'm probably being really daft, but I can't find the default view property. I've tried selecting the subform, and opening it in a new window, but I can't find it anywhere...

FWIW I do use Access' help, but it never seems to explain exactly what I'm after.

Thanks a million

 

[KenHigg]

Open the subform in design view and right click on the small square in the upper left most portion of the screen. Select properties and it should be the sixth option from the top.

???
kh

 

[freda42]

thats it! I don't know what I was doing before because it wasn't there when I looked.

Thanks