Showing query results in forms

[Fluffygoldfish]

Hi

I am fairly new to access, but have managed to create a form and link various tables and queries to it. I have a table with a date of referral and another with client info in. I have created a query to calculate the age of the client at date of referral, but cannot find the best way to put this on my main form. Ideally I would like to have a sub dataset with Date of Referral, Age at Referral, Discharge (plus other related columns). I put the query in the form to show all of this which was fine. But sometimes clients are re-referred and it wont let me add a record to the subquery.

Perhaps I'm going about it all the wrong way, but I need something similar to a table you might have in excel with automatically calculated fields.

Can anyone shed some light on this please?

Thanks

[beacon]

What tables are you using and what are the fields in each?

It sounds like you need a Client table and a Referral table and use a subform to show all the referrals for a client. Thay way, when you go to client #1, you will see that the client had a referral 10/01/2007, 02/23/2008, and 04/11/2009.

If you have the client's birthdate in the Client table, you can use an expression on the form to calculate the Age at Referral and have the control source link to a field on the Referral table.

[NeoPa]

Welcome to Bytes!

I suspect you would benefit from reading Normalisation and Table structures.

I say this because (not simple guesswork I assure you) you seem to be a bit wooly on the data structure you need. This article gives help with how to go about asking yourself the right questions for this.

It's true to say that the design of you tables, your data structure, is the most important part of your database.

Until we (and you) know the data structure that best fits your scenario, it's hard to know what to recommend. It may be a subform type setup, but maybe not.