Access 2003: Can't update from query

[WestofYouLB]

I have changed (as many told me I should) from a list of employees and another list of applicants, to a list of People including both sorts of folks.



I made a query with the list of Applicants joined one-to-many to a list of References by Badge. I could append data on each applicant, uniquely, and have several references. No problem.

But since I have changed to a single list of People, I can no longer append additional References[Names], for instance, under the same Badge number (the common field that links most of my stuff).

BUT I note that the query People>>References actually HAS several instances of many previously entered records where the Badge and some info on the People record shows in the query, along with whatever unique References records as exist.

Badge is unique in People but not in References, which it probably should be. But Access won't allow me to append new records (by filling in People[badge number] in the query). Used to be, when I filled in Applicant[badge number] it would automatically fill in some Applicant information, then I could happily fill in the References info, but no more. When I get to the References fields, I can't enter any information. A loud sort of Doink sound, instead.

Thanks.

Don.

[kennejd]

I have changed (as many told me I should) from a list of employees and another list of applicants, to a list of People including both sorts of folks.

I think the suggestion was intended for you to create a People table....then a separate Badge table...then a People_Badge table...since there's a one-to-many relationship between badge and people.

You're having trouble updating from the query you created because of the multiple tables involved, I believe. I don't think you're current setup is going to work quite the way you want.

[WestofYouLB]

I have changed (as many told me I should) from a list of employees and another list of applicants, to a list of People including both sorts of folks.

I think the suggestion was intended for you to create a People table....then a separate Badge table...then a People_Badge table...since there's a one-to-many relationship between badge and people.

You're having trouble updating from the query you created because of the multiple tables involved, I believe. I don't think you're current setup is going to work quite the way you want.

kennejd,

Thanks for replying. Maybe we're getting somewhere.

There is no Badge for References, as a unique identifier. The references' Badge is the link back to the People table to link a person in the People table they refer to.

But someone here said that what I need is a form (that's what I had earlier, and can't find a way to recover it) where I fill in the Badge and it calls up (without actually adding data to the People table) data like Badge, First/Last names, etc., but the data doesn't get written back to the People table. The only write would be to the References. Which is what I used to be able to do and no longer can.

This notion of typing in the Badge and getting info from the People table has mystified me since I first did it, and I always feared this would happen: the form goes away and I can't remember how to do that trick again.

I have better notes as I go along, but this one I don't have.

Don.

[kennejd]

Can you post some sql or a pic of your relationships? I'm having a hard time following.

Seems like maybe you'd have a form based on Badge number....with a subform that lists related people. Then you could have a combo box that you use to do a search...just use the wizard.

[WestofYouLB]

Not sure how to do that, but Monday maybe I can sort out a way. Thanks for replying.