Simple update query question

[George Hewitt]

Hey all,

I am currently struggling with the following. I have created a table which has columns which are auto populated by related fields using lookups.

For example: I have an Activity tbl (for advertising activities) and a Publication tbl (which is used to select which publication the advertising activity is part of)

Ok so I have created a joined field using an update query as a unique identifier (which needs to be used for other purposes on other systems/referals.) I used the following SQL to achieve the result i wanted however instead of obtaining the lookup information (i.e. the publication name) its calling the unique identifier i.e. the publication ID (which is autonumber)

UPDATE Activity SET SRC_ID = Publication_ID & [Activity_ID];

I want it to select the first 3 digits of publication name not ID! I have fiddled around with different things but i haven't got much experience in SQL if anyone can help me/guide me it would help me alot.

Thank you

George

 

[plog]

I have created a table which has columns which are auto populated by related fields using lookups...I have created a joined field using an update query as a unique identifier

If I understand you correctly, you're doing it wrong. It sounds like you are trying to move data from one table to another--that's not the way a database works. Instead of an UPDATE query, you simply use a SELECT query, join the appropriate fields between the tables and use that query when you need the data combined.

If you can post sample data from all relevant tables (including table and field names) and then what you want the end data to look like, we can help build that SELECT query. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 13, 2/1/2009
Steve, 41, 3/17/2008
Sally, 5, 4/4/2008

 

[George Hewitt]

Ahh ok thanks for the help, Yep i just relaised i shouldn't be using lookup values all over my database

I'm going to approach the select query tommorow but i will post if i need any assitance.

 

[jdraw]

Why exactly do you want this "concocted" code? Most often it is best to use atomic data in data bases--that is 1 fact, 1 field. Too many people try to "create these fields" that they feel offer "added value". Form experience 1 fact, 1 field is much easier to maintain; select info from; update...

You can select info then display concatenations/formats/... anything you want.

Please step back and tell us in plain English about your business issue/opportunity.

 

[George Hewitt]

Ok ill try explain! Well the system before the one im creatng now uses a 'sourcecode' it was something they made up. I suggested like you said just to use an atomic simple autonumber right but they needed to have this 'significant unique code' for each marketing activity (the code is publication which is the magazines first three letters with a random numeric code.)

So what i have done is made an autonumber on the Activity table which makes every marketing activity unique i am now currentlty trying to make a joint field which combines the values of Publication Name and Activity number to use this as a unique index?

Does that make sense but the thing is i know i don't need to use this 'made up value' on the database but its something that is needed on other systems and as part as a business process.

I hope that explains something! Thank you again for your help so far

George

 

[plog]

i don't need to use this 'made up value' on the database

So why are concerned with it?

If you are in charge of generating it, simply take the first 3 characters of the Publication name and then concatenate the Activity_ID to it:

SourceCode: Mid([PublicationName], 1,3) & "-" & [Activity_ID]

 

[George Hewitt]

So why are concerned with it?

If you are in charge of generating it, simply take the first 3 characters of the Publication name and then concatenate the Activity_ID to it:

SourceCode: Mid([PublicationName], 1,3) & "-" & [Activity_ID]

Very true. My main question was in fact about the SQL programming as i have no previous experiences. Sorry to be a pain

UPDATE Activity SET Activity.SRC_ID = Mid([Publication_Name],1,3) & "-" & [Activity_ID];

Publication_Name is in the Publication table was i meant to enter that somewhere?

 

[plog]

Again, this shouldn't be stored, ergo you should not be using an UPDATE query for this. Use a SELECT query. SRC_ID is not a field that should be in your tables, it should be a field you calculate in a query.

In design view of a query bring in your tables, join them appropriately then use my SourceCode code to generate the information you want.

 

[George Hewitt]

Thanks Plog for your assistance