Full Version: Base A Datasheet Form On An Aggregate Query
UtterAccess Forums > Microsoft® Access > Access Forms
tmort
I have a table with student information from previous years classes. I'd like to use some of this information to partially fill in the form for the current years classes.
uite often the same child appears in this table several times for different years. One of the fields is Age as of class year.
I thought I could base a form in datasheet view on an aggregate query that groups be childs name and selects the maximum age to get the most recent record.
The query works to do this, but, if I base a form on this query I get all the records.
Is there a way to do this?
projecttoday
Are you saying that the results of the query changes depending on whether or not a form is bound to it?
tmort
If the table has three records and one of them is mary smith twice, onc when she was nine and once when she was ten and I run my query I get Child one and mary at 10.
If I base a form on this query I get all three records, child1 and mary at 9 and mary at 10.
projecttoday
That doesn't happen. Post the database. Also, you should consider redesigning your tables.
tmort
Redesigning the db at this time would be difficult.
projecttoday
Both the form and the query show 3 records.
If you proceed with a bad design, it will not only be more difficult to work with that database but some things you will want to do will be impossible.
JonSmith
You need to redesign your table structure, look into what it means to have a normalised database and relationships between tables. Do that and we can solve this issue very easily, if you insist on keeping this format then although we can advice you on how to make it work under the current structure you will encounter numerous other issues down the line in development and solidify bad habits for the future that will impede future efforts.
ts better to invest this extra time early on to understand how to get it right first time.
Jon
tmort
Unfortunately the db has to be used right now. I am volunteering and am very strapped for time. I can normalize it fully later.
did find my problem though. The query in the example and the form based on it were wrong. That query was not the aggregate query I was referring to. That query only returns two records. However, both in the example I uploaded and in my real db I had the form based on the non-aggregate query (it has a similar name).
So I guess I solved my short term problem.
Thanks
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.