Hypothetical question on parameter and update query

[Beemerang]

Hi All

I have a table containing schools. I have another table that contains sets of enrollment data which have been collected for these schools. These sets of enrollment may be collected on several different dates, but all the records with the same date, belongs to the same "collection set", if you will.

Would it be possible to create an update query that prompts the user for a date parameter and then matches the set of enrolment records tied to that date and enters the enrolment data for that school into fields in its record in the school table? I don't want to waste hours trying this if there are good reasons why it won't work in principle.

Cheers
Beem

 

[RainLover]

If I understand your objective correctly then I can't see a problem with the principal you described.

 

[Beemerang]

If I understand your objective correctly then I can't see a problem with the principal you described.

Thanks for the quick response! Now for the million dollar question, any suggestion on the best way to go about it? I haven't worked in Access for years so any advice would be welcome.

Thanks again,
Beem

 

[RainLover]

Hi All

I have a table containing schools. I have another table that contains sets of enrollment data which have been collected for these schools. These sets of enrollment may be collected on several different dates, but all the records with the same date, belongs to the same "collection set", if you will.

Would it be possible to create an update query that prompts the user for a date parameter and then matches the set of enrolment records tied to that date and enters the enrolment data for that school into fields in its record in the school table? I don't want to waste hours trying this if there are good reasons why it won't work in principle.

Cheers
Beem

You description of exactly what you want to do is vague. From what I can gather you have a Table that has a list of many schools.

You have another table that has enrolment data which has the same Date collected on different Dates. I don't understand this one.

However you want to group records by collection date and if it matches something then put it somewhere.

You only have two tables, is this right.

Perhaps you could fill in some of the missing bits for me.

Access is one of those things that can take extra time until you get the swing of it.

 

[Beemerang]

You pretty much grasped it perfectly despite my poor explanation!

The only correction: I have several sets of enrollment datA for the list of schools collected on different dates. So I might have 10 enrollment data records for the same school with each set of data having been collected at a different time of the year.

So I have a school table which has fields such as Enrollment Number (of Students) for each grade. These fields are currently manually populated with data. My aim is for the query to match the enrollment data for each school as at the date the user is prompted for when they run the query, and then update the school table with the correct enrollment numbers for each school.

Does this make more sense? Thanks again for taking time to reply!

B

 

[RainLover]

What have you got so far?

Have you designed a set of tables.

So I have a school table which has fields such as enrolment Number (of Students) for each grade.

This leads me to believe you have at least one other table.

Please post a pic of your relationships or what would be better is a copy of your actual database. It needs to be in Version 2003 as I cannot open anything in a later version.

 

[Beemerang]

I'm posting a pic of the relationships as the database is pretty big, I hope this helps.

Thanks mate!

Beem
PS: I am about to go into a couple of meetings so I will reply to any further messages as soon as I can.
B

 

[RainLover]

That tells me very little except that there is definitely some design faults.

The problems is now, do you want to go back and start again.

Please get back after having a good think. Perhaps you could google for uTube tutorials on Access Design or Access Normalisation.

Take you time. I am off for a while.