Update a Table's Contents

[andy_dyer]

Hi,

I have a stored value in a table that I need to duplicate in every record.

The way the database is designed is that I have chosen from a combo box a location which is centrally stored in the database.

This is then displayed on the input form frmInput.

This is fine but what i need to be able to do is to update the table tblInput field Centre with the contents of this field Location from tblLocation.

I have tried the lookup wizard with no success.

If I can't find a better way, i will have to routinely export to excel and then do a replace all on the empty fields under Centre.

Pretty please with a cherry on top does anyone have any suggestions for me?????

Thanks,

Andy

 

[Fornatian]

andy,

why would you want to duplicate the value across all records when by definition it is the same for all records, it is a bad approach.

Instead, when you need this value either do a DLOOKUP for the value from a reference table or include your reference table in your objects source query - ensuring there is only one value returned by the reference table you will end up with a dataset matching your requirements.(this will actually cause a cartesian result which is why you need to ensure only value is returned by the table)

 

[andy_dyer]

Hi Fornation,

Thanks for responding...

Not wanting to appear thick (bit late now...) but I did not understand any of that!

Is a reference table different to a normal table?

What's a object source query?

I know what I was proposing wasn't ideal, so I really am open to suggestions!

Thanks again,

Andy

 

[namliam]

Andy,

I would suggest you look into an Update query to get the results you want (test it on a copy first !)

Regards

 

[andy_dyer]

I have been playing with this already but with no success...

code is at present:

UPDATE tblLocation LEFT JOIN tblInput ON tblLocation.Location = tblInput.[Centre Code] SET tblInput.[Centre Code] = [tbllocation]![Location];

sometimes when i run it, it adds a record and places the location in the correct field, other times it does nothing...

I either need a way that it does this every time a new record is added or on mass when I need to export the data into excel for transport.

Thanks

Andy

 

[namliam]

Sounds more like you need a default value rather than a update afterwards

You are linking/joining Location = [Centre Code]
Then setting [Centre Code] = Location

ILeft Join means It must be present in tblLocation but not need be present in tblInput

Therefor it does not seem to do anything if it is allready present in tblInput

You might be better off doing an Insert query ...

Regards

The Mailman

 

[andy_dyer]

Hi Namliam,

There doesn't appear to be a smiley for tears of frustration!

You might be better off doing an Insert query ...

What's an Insert Query??

Thanks for your help, you have no idea how much it is appreciated!!

Andy

 

[namliam]

Insert query is a query to append new records to a table, which is what you are doing, isnt it?

Post a sample of the DB (the 2 tables involved and an example in it for what you want to do....) Because i think we are not understanding eachother properly and 1 picture (or DB) shows more than a 1000 posts can tell..

Regards

The Mailman

 

[andy_dyer]

Here it is...

due to the size of the database have only left tblInput and tblLocation and the update query that I had started...

Thanks

Andy

 

[andy_dyer]

I have also left in some sample data, thought that would make it easier!

There is a location in tblLocation and I need to get that in every instance of Centre Code for every record in tblInput.

Hope that helps,

Andy

 

[namliam]

I seem to remember helping you on this DB before...

I then gave you advice to NOT use spaces and/or special chars in any names, But you didnt listen did you?

Well here it is again: Do NOT use spaces and/or special chars in any names. It is a bad habbit you should break ASAP.

I am still unsure as to what you are trying to do.

To copy from tblLocation to tblInput try:
1)
UPDATE tblLocation, tblInput SET tblInput.[Centre Code] = tbllocation!Location;
2)
UPDATE tblLocation, tblInput SET tblInput.[Centre Code] = tbllocation!Location
WHERE (((tblInput.[Centre Code]) Is Null));

To copy from tblInput to tblLocation try:
3)
INSERT INTO tblLocation ( Location )
SELECT tblInput.[Centre Code]
FROM tblInput LEFT JOIN tblLocation ON tblInput.[Centre Code] = tblLocation.Location
WHERE (((tblLocation.Location) Is Null))
GROUP BY tblInput.[Centre Code];

See if any of those help...

Regards

 

[andy_dyer]

Thanks Namliam!!

It was (2) that i wanted!!

That has worked a treat!!

Do you mean that my field names should have any spaces or things like "?" in them?

I didn't know this was bad, does this complicate things?

I have moved to naming conventions as I have discovered them for my forms but didn't know I needed to for my tables.

I'll have a hunt about on this forum and see if I can find any conventions that I can use!

Thanks again for your help!

Andy

 

[namliam]

No * ( ) * & ^% $ # @ ! and a space should appear anywhere in you app.

Not in VBA and not in any names whatsoever. Instead of Customer number try CustomerNumber

stuff like that...

Regards

 

[andy_dyer]

Thanks Namliam!

Will try to do better in future!

Thanks again for your help!

Andy