Help with Update Query

[OpIvy1403@aol.com]

I have a database that I am using to schedule applicants for tests. I have an applicant table, and a schedule table. I also have a form that is the applicant entry form. What I have them do is enter a site ID and schedule ID into the form, among other information. What I am trying to do and hping someone can help me with is when that is entered in, that the field in the schedule table, applicantscheduled, is updated. And lastly there is a field in that same table called quantity reserved that hold the maximum number of applicants allowed to be scheduled, there is also a field that is the status of the site. And I would like the query to update that field to closed when the max number of applicants have been scheduled. Hopefully this explains what I am trying to do.

Thanks for any and all help!

 

[Ziggy1]

when that is entered in, that the field in the schedule table, applicantscheduled, is updated.

This part sounds like you are trying to duplicate data that is already entered. Related data should not be copied to another table when it can be referred/linked to.

It is easier to help if you can post a sample DB ( no personal info)...strip out anything that is not relevant.

 

[OpIvy1403@aol.com]

I am trying to upload a sample version of what the database is doing but I have not had any success since you posted, I keep getting a database error on this sites side of things when I attempt to upload it.

http://groups.google.com/group/devine-in-pahttp://sites.google.com/site/opivy1403/files/RecruitingSmp.mdb

this is a url where I posted the sample.

 

[Rabbie]

I am trying to upload a sample version of what the database is doing but I have not had any success since you posted, I keep getting a database error on this sites side of things when I attempt to upload it.

http://groups.google.com/group/devine-in-pa

this is a url where I posted the sample.

First Compact and repair your DB and then ZIP it before sending it.

 

[OpIvy1403@aol.com]

Ok here is the database, I removed as much as I can to try and help give you an idea of what I am trying to do. Also it may need to have you enter a site into the site table to be able to make it work. I had to remove the form to meet the attachment size criteria.

Thanks,

Stephen

 

[Ziggy1]

Hi Stephen,

I took a look ( I had to recreate the subform), and I am not clear what value and from where the "applicantscheduled" field is to be populated. I see you have an Applicatant ID field also, and this is still leading to the point I was trying to make.... It would appear to me that in the subform you should have a combo that selects the applicant from the appicant table...that way it is "Related" and there is no need to do any updating.

 

[OpIvy1403@aol.com]

Hi Stephen,

I took a look ( I had to recreate the subform), and I am not clear what value and from where the "applicantscheduled" field is to be populated. I see you have an Applicatant ID field also, and this is still leading to the point I was trying to make.... It would appear to me that in the subform you should have a combo that selects the applicant from the appicant table...that way it is "Related" and there is no need to do any updating.

The applicantscheduled field is being populated only by entering it in manually, What we wanted to do is to populate the field through the applicant entry form, by selecting the site id and scheduled id for it to automatically update the applicant scheduled field. Is that possible?

 

[Ziggy1]

Ok,

I made some changes to the database to try to explain what I mean. The way I have isn't nescesarily the way you might want it, but just take a look.

As I see it first a site is created, then you create the Schedule details..each site has MANY schedules? I base that on the fact that you had a subform with those 2.

Next the Schedule can have more than one Applicant, because you want to add them up...therefore you need a Detail to the Schedule table...I took the Applicants entry form and changed it around a bit and made it a SUB to the SchedDetails table.

In the Applicants detail table design, I removed the field for SITEID because you have the field for Schedule ID which in turn is already related to the SiteID. I modified the lookup so you will see the SiteId at the same time in the browse. I added a control in the App form to count the entries, by itself won't make sense but if you open the Site Schedule form you will see it is used in an unbound control (the blue one) to calculate the applicants.


I know I am making assumptions, but looking at your design, I see difficulty in doing it the way you want...you have the "ApplicationID as a field in the ScheduleDetails table...that can only hold ONE record? * Thats why I added the detail


* I was getting errors sometimes when making certain changes so I don't know if it has anything to do with you having removed some things...sometimes the form would freeze after making a change....maybe just my system?