Update query Problem (1 Viewer)

ct2013 · Oct 27, 2013

Hi there,

I have three tables:
Students
Parents
ClassSplits

In my Students table i am making a field called SDD, with which i want an update query to place in there form that table 2 field and 2 field from Parents table and 1 from ClassSplits.

My update looks like this: UPDATE TO:
[students].[SName] & ", " & [ClassSplits].[CClass] & ", " & [Parents].[Paddress2] & ", " & [Parents].[PAddress1]

All the relationships are set up and working, prob being when i run it, the db looks at [students].[SName] (the field from that table to which the update is happening) looks at it as if it is a question query, so when i run it it comes up with a pop up box saying SName with a text box under it, but all the other fields update!

Please help ASAP.

Ta

ct2013 · Oct 27, 2013

I can see that this is only happening once i add other tables to my query, but if i just keep the table i am updating it updates with field from that table no problem!

RainLover · Oct 27, 2013

It appears to me that it does not recognise or cannot find sName.

Suggest you delete this field and then replace it. There may be a space or a dot missing. So if this is the case rebuilding it should fix it.

ct2013 · Oct 27, 2013

Hi there,

i thought that, but if i got rid of the other tables in the query then it worked fine.

is it a bug??!!?!?!

---------
MS Access has far to many bugs!

RainLover · Oct 28, 2013

ct2013 said:
Hi there,

i thought that, but if i got rid of the other tables in the query then it worked fine.

is it a bug??!!?!?!

---------
MS Access has far to many bugs!

It is not a Bug.

Access does not have any undocumented bugs that I know of. I can only say this with confidence up to Access 2003.

Can you post a copy of your problem in a 2003 sample database.

I need to see more of what is going wrong.

ct2013 · Oct 28, 2013

Sorry i only have access 10

Pat Hartman · Oct 28, 2013

What you are attempting to do doesn't make any sense aside from violating normal forms that warn against storing duplicate data and keeping non-atomic data.
Students has a 1-many relationship to parents and a SEPARATE, UNRELATED 1-many relationship to ClassSplits. Joining these three tables results in a Cartesian Product which by definition is not updateable.

When you take many-side data and store it in the one-side table, exactly which row of the many-side table did you want to store?

Just because two tables have what seems like the same FK (StudentID) doesn't mean that it makes sense to join them.

ct2013 · Oct 28, 2013

I hear,

My biggest problem is now that i cant make an append query with a field that has a multiple list box. only the first of the multiple list comes in the appended table!
Any ideas of coding needed?

Thanks

RainLover · Oct 29, 2013

ct2013 said:
I hear,

My biggest problem is now that i cant make an append query with a field that has a multiple list box. only the first of the multiple list comes in the appended table!
Any ideas of coding needed?

Thanks

Did you really understand what Pat said. I get the feeling that you didn't.

Do you have a List Box in your Table, as part of a field. If so this could be your problem.

Again I would suggest that you attach a copy in Access 2003 and I will have a look. To work out how to convert look at Access Help or Google it.

Pat Hartman · Oct 29, 2013

only the first of the multiple list comes in the appended table!
Any ideas of coding needed?

Multi-value fields are nothing but trouble. I suggest ditching it and creating a proper, normalized schema that you will be able to manipulate with standard SQL. You will need to change your form to remove the multi-select list box and replace it with a subform.

RainLover · Oct 30, 2013

ct2013 said:
Sorry i only have access 10

I wasn't thinking correctly.

Version 10 is Access 2002.

This format is fine to post.

If you need help to attach the Database there are instructions in the FAQs, or if you run into trouble let me know the problem and I will help you with it.

Update query Problem (1 Viewer)

ct2013

Registered User.

ct2013

Registered User.

RainLover

VIP From a land downunder

ct2013

Registered User.

RainLover

VIP From a land downunder

ct2013

Registered User.

Pat Hartman

Super Moderator

ct2013

Registered User.

RainLover

VIP From a land downunder

Pat Hartman

Super Moderator

RainLover

VIP From a land downunder

Similar threads

Users who are viewing this thread