Update Queries after Table Field Name Changes

In Access 2007 I have a set of tables in which the name of the index field 
has been changed.  How can I update the queries, based on these tables?

In data view I get the 'Enter Parameter Value' box. Enering the new field 
name in various formats gets me no results.  Trying to get into design view 
the error box
'Databaser can't represent the join expression Table1.oldname=Table2.oldname 
in Design View' appears.  Underneath it says 'one or more fields may have 
been renamed'.  Yes, I know.  But, how can I update the query to the new 
field names?

Any help would be appreciated.
0
Utf
2/11/2008 8:59:40 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
744 Views

Similar Articles

[PageSpeed] 47

Peter

If you are trying to do this in "data view", you probably won't succeed.

Try opening the query in "design view".

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Peter K" <PeterK@discusions.microsoft.com> wrote in message 
news:C309DCA2-D8CA-401B-BEE4-588F37072557@microsoft.com...
> In Access 2007 I have a set of tables in which the name of the index field
> has been changed.  How can I update the queries, based on these tables?
>
> In data view I get the 'Enter Parameter Value' box. Enering the new field
> name in various formats gets me no results.  Trying to get into design 
> view
> the error box
> 'Databaser can't represent the join expression 
> Table1.oldname=Table2.oldname
> in Design View' appears.  Underneath it says 'one or more fields may have
> been renamed'.  Yes, I know.  But, how can I update the query to the new
> field names?
>
> Any help would be appreciated. 


0
Jeff
2/11/2008 11:13:25 PM
That's exactly what I'm trying to do.  However, the above mentioned error 
message appears and won't let me get into design view.

"Jeff Boyce" wrote:

> Peter
> 
> If you are trying to do this in "data view", you probably won't succeed.
> 
> Try opening the query in "design view".
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Peter K" <PeterK@discusions.microsoft.com> wrote in message 
> news:C309DCA2-D8CA-401B-BEE4-588F37072557@microsoft.com...
> > In Access 2007 I have a set of tables in which the name of the index field
> > has been changed.  How can I update the queries, based on these tables?
> >
> > In data view I get the 'Enter Parameter Value' box. Enering the new field
> > name in various formats gets me no results.  Trying to get into design 
> > view
> > the error box
> > 'Databaser can't represent the join expression 
> > Table1.oldname=Table2.oldname
> > in Design View' appears.  Underneath it says 'one or more fields may have
> > been renamed'.  Yes, I know.  But, how can I update the query to the new
> > field names?
> >
> > Any help would be appreciated. 
> 
> 
> 
0
Utf
2/12/2008 2:44:01 AM
Peter

It may not be elegant, but sometimes I just throw away the old one and start 
over.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Peter K" <PeterK@discusions.microsoft.com> wrote in message 
news:67007A72-A0EC-4FD5-A162-BC62975C51B4@microsoft.com...
> That's exactly what I'm trying to do.  However, the above mentioned error
> message appears and won't let me get into design view.
>
> "Jeff Boyce" wrote:
>
>> Peter
>>
>> If you are trying to do this in "data view", you probably won't succeed.
>>
>> Try opening the query in "design view".
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Peter K" <PeterK@discusions.microsoft.com> wrote in message
>> news:C309DCA2-D8CA-401B-BEE4-588F37072557@microsoft.com...
>> > In Access 2007 I have a set of tables in which the name of the index 
>> > field
>> > has been changed.  How can I update the queries, based on these tables?
>> >
>> > In data view I get the 'Enter Parameter Value' box. Enering the new 
>> > field
>> > name in various formats gets me no results.  Trying to get into design
>> > view
>> > the error box
>> > 'Databaser can't represent the join expression
>> > Table1.oldname=Table2.oldname
>> > in Design View' appears.  Underneath it says 'one or more fields may 
>> > have
>> > been renamed'.  Yes, I know.  But, how can I update the query to the 
>> > new
>> > field names?
>> >
>> > Any help would be appreciated.
>>
>>
>> 


0
Jeff
2/12/2008 4:17:12 PM
I found a slightly more elegant solution:

Your response, last night made me think, there has to be a way to get to 
design view directly & not just after opening the query, which brings up the 
data view.  So I right clicked on a query & select design view there.  That 
brings up the same error mesage.  However, after you kill that an SQL (I 
think it's SQL) version of the design view comes up & there it's a cinch to 
change the field names to the new ones.

Thxs for your help.  Peter K

"Jeff Boyce" wrote:

> Peter
> 
> It may not be elegant, but sometimes I just throw away the old one and start 
> over.
> 
> Good luck!
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Peter K" <PeterK@discusions.microsoft.com> wrote in message 
> news:67007A72-A0EC-4FD5-A162-BC62975C51B4@microsoft.com...
> > That's exactly what I'm trying to do.  However, the above mentioned error
> > message appears and won't let me get into design view.
> >
> > "Jeff Boyce" wrote:
> >
> >> Peter
> >>
> >> If you are trying to do this in "data view", you probably won't succeed.
> >>
> >> Try opening the query in "design view".
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Peter K" <PeterK@discusions.microsoft.com> wrote in message
> >> news:C309DCA2-D8CA-401B-BEE4-588F37072557@microsoft.com...
> >> > In Access 2007 I have a set of tables in which the name of the index 
> >> > field
> >> > has been changed.  How can I update the queries, based on these tables?
> >> >
> >> > In data view I get the 'Enter Parameter Value' box. Enering the new 
> >> > field
> >> > name in various formats gets me no results.  Trying to get into design
> >> > view
> >> > the error box
> >> > 'Databaser can't represent the join expression
> >> > Table1.oldname=Table2.oldname
> >> > in Design View' appears.  Underneath it says 'one or more fields may 
> >> > have
> >> > been renamed'.  Yes, I know.  But, how can I update the query to the 
> >> > new
> >> > field names?
> >> >
> >> > Any help would be appreciated.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/12/2008 4:48:00 PM
Reply:

Similar Artilces:

Copy Formula with Reference Change
I have a workbook that will have 100+ sheets in it. I want to use a beginning number in a cell on the first sheet and then on the following sheets in the same cell have a formula that adds 1 to the number in the same cell on the previous sheet. For example, the first sheet has 497 in cell B2. In each successive sheet, I would have a formula in cell B2 that would be '=sheet1!B2+1' to give me 498, then 499, then 500, etc. I want to copy the formula to each successive sheet and have it automatically adjust the sheet it is referencing to be the previous sheet. However, when ...

changing c sharp background
hi; i want to set a picture to the console application if u know how plz answer me i know how to chang the background color but i dont know how to set a picture to it thanks for all answer ... :) A console application ? Doesn't seems to make sense as this is a character mode device... Else please be more specific (you want to use a picture as a background for a System.Windows.Forms.Form object ?) -- Patrice "sahel" <nam.nam.barooon@gmail.com> a �crit dans le message de groupe de discussion : 8e8e7758-2a82-436d-8e76-6ea5acabb025@15g2000yqi.googlegro...

Converting/inserting values, without changing the other contents.
Hi all this is what i require: the overdue is INR 250k (SGD 7,543), out of which for INR 125k (SGD 3,771) payment is processed, for INR 30k (SGD 905) bills resubmitted for queries, for INR 25k (SGD 754) supporting were resubmitted, for INR 45k (SGD 1,358) payment will be made in 1week of next month. the above comments will be in m5 without the sgd value, i need help to converrt those values and show as above in n5, after multiplying the converting factor at b2 thank you ...

Please help. Question about querying a subform
I asked this yesterday and see all kinds of questions around me being answered and suppose i just did not word this correctly. I have a table of lab results. It is related to my patient table. It is a one-to-many relationship. I have a date field, which is the date of last labs. I am trying to build a query that will pull only the most previous lab from each patient, with the criteria of it having been more than 6 months ago. I do not want to pull lab dates from 10 months ago, if there is one within the last 6 months. This is for a Dr and it is a priority that I get it d...

Track Changes History Worksheet
In a shared password protected workbook, I would like to keep the History worksheet displayed all the time. Currently, every time I open up the workbook, I have to go to Track Changes and click on List Changes in a New Sheet. Thank you for your help! ...

Check Names
Using Outlook 2000 under Windows 2000 Server. When I type a partial name in the To: field on a new email, I get the red squiggly line indicating there are multiple names matching that pattern. When I hit Send, I get the Check Names window showing a list of matching names. However the only names that show up in the list are from the Global Address List, and none from my Contacts. Under Tools, Services, Addressing tab, I have two entries in the "When sending mail, check names using these address lists in the following order". First is the Global Address List, and second is Contacts,...

Copy one field in two or more fields
Hi i have a tableA with one field and another tableB with two fields. TableA Name a b c d e f .. ... I want copy all values alternatively, in an another tableB with two fields. The results should be: TableB NameA NameB a b c d e e .. Anyone can be help me ? Thank you in advance Best regards Diego -- Message posted via http://www.accessmonster.com Diego via AccessMonster.com wrote: >i have a tableA with one field and another tableB with two fields. > >TableA >Name > a > b > c ...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

Pivot Tables
Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% ...

How to update one form when closing another ?
I have two forms open. frmPeople has the Focus frmAccount does not. frmAccount has subFormPeopleList I add a new person to frmPeople (which is linked to frmAccount) Question 1: When I close frmPeople I want to update frmAccount so the new person shows in subFormPeopleList Quetion 2: But I'm also wondering if I add code to the "close event" for frmPeople to update frmAccount What will this do if frmAccount is not open? Will it add the person twice? I'm guessing not as updating a form doesn't really add data to the table... right? Thanks for any help. Mel Thanks...

Changing the "reply to" on emails
I have several support reps who monitor a queue for customer support requests and then reply to the customer using a CRM email activity. We want any customer reply to our email to be sent to the email address asssociated with the queue. Currently, the emails we send via the CRM use the support rep's email address for the "sent from" and "reply to" addresses. The problem is that when the customer replies to that email, the reply is sent to the support rep's outlook vs back into the support queue. The support rep could be on vacation, etc. I tried settin...

Accessing Custom fields (propery)
Hi everyone, Any one who can tell me how to access custom fields using the CRM web service? Thanlks, ...

Changing Outlook from Exchange Services to IMO version
How can you reinstall Office 2000 and change the Outlook "type" from using Exchange/Internet Mail to Internet Mail Only (IMO)? I uninstalled my version of Office2000 and reinstalled it from my CD. However it doesn't give me the option to install the IMO version of Outlook. It installed the Exchange/Internet Mail automatically. Without an exchange server the checking of email is dog-slow. Thanks, Alex Moylen Hi, Alex. Alex Moylen ha scritto: > How can you reinstall Office 2000 and change the Outlook > "type" from using Exchange/Internet Mail to Internet Mail ...

How to copy a field of A excel file to B Excel file automatically?
I need to copy some fields of A.xls to B.xls every night at 12:00. how can I do that? I'd try one of two methods. First, use the MS Windows "scheduler" to fire off you spreadsheet at midnight (make sure to leave the computer turned on) and have a VBA macro triggered when the workbook is opened to perform the data movement. A second, albeit more difficult method, would be to write VBA code to structure an internal timer that would watch for midnight. In this case, you'd have to open the workbook before going home for the day, and activate the macro that sets ...

Field names?
Can anyone tell me what type field and the field names are for the Vote Tracking Recipient and Response? I do see one called "Voting Response" under "All Mail Fields", however that is only showing me my vote responses to someone else, i.e. not thost that I've sent out for a vote myself. Please help, Steve The Voting Response field works for me in my Inbox when I recieve votes in response to an email I send out with Voting Buttons on it. Judy Gleeson MVP Outlook Trainer and Consultant read my articles here: www.judygleeson.com Canberra, Australia how to post qu...

Comparing queries
Hi, I am using a database storing equipment for rooms where I have two queries set up, one listing original equipment and another listing current equipment for room layouts. The two queries work fine independantly, but I now want to compare differences between these where I need to know: 1 - equipment in both original and current rooms. 2 - equipment in original room now missing from current room. 3 - equipment in current room, not required in original room. Regards, Dave. You did not say anything about the table structure so I will go with what you posted. Use a unio...

Code Union Query
I have a Union Query looking like this: SELECT ALL * FROM[invoicetrading]; UNION ALL SELECT * FROM[invoicesnontrading]; I would like that the query is sorted by accountnumbers. I.e. I have accountnumbers like 43001000-6000 - and I would like that the query is sorting them on the last four digits (Something like: Sort *####). How can I alter my code so that it is done automatically? Thanks Klaus Can we assume that both your tables have the same number of fields, of the same type, in the same order, and that one of those fields is called AccountNumber? Try something like this: SELE...

How to read values from a table?
Hi, I am trying to redesign an old database. I have three forms start, entry, edit forms. Start form is like a switch board to enter into the forms. An audit is entered into the database through "Entry" form which has around 50 controls. All these controls gets stored in the main table. "Edit" forms allows you to edit the audit. The issue here is the "Edit" form doesn't read the values from the main table when the form is loaded. I have a logic to read the values from the main table in "start" form. Everything looks fine with the SQL statemen...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

dynamic charts using named formulas
I have dates in the first row I have values in the second row I use tecnique of named formuls of offset function (tushar Methta) so that the chart is updated when data for new dates are added. now after some time I want to delete the older data If I delete the columns of older dates the named formula get messed up and I get error message that three is some wrong reference. actulally in the offset formula the starting cell is relaced by "REF" Then I have to redo the whole exercise any solutions. If the first row is some sort of header that doesn't get deleted, use it as the ba...

Rename name so formulas show name change
Excel 2003 Cell A1 is named MyCell. Cell A2 has formula "=MyCell". I rename cell A1 in the toolbar to NewCell. Cell A2 still has the formula "=MyCell". How do I rename cell A1 to NewCell and have it automatically change cell A2 to the formula "=NewCell"? Thank you for your help. Download Jan Karel Pieterse'e Name Manager here: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "John" <no.email@no.email.com> wrote in message news:ORne6k3$GHA.4592@TK2MSFTNGP03.phx.gbl... | Excel 2003 |...

Icon won't stay changed!
I use properties to change the icon on one of my desktop icons. It appears to accept the change, but when I go back to my desktop, the original icon appears - it ignores my attempted change. This is on my up-to-date XP on my new Acer netbook. TIA, Paul I use properties to change the icon on one of my desktop icons. It appears to accept the change, but when I go back to my desktop, the original icon appears - it ignores my attempted change. This is on my up-to-date XP on my new Acer netbook. TIA, Paul (I've posted this on multiple groups, because my original post on...

Pivot Table 2007 display field only in Grand Total
Dear Excel experts, I have a Pivot Table (Excel 2007). As an example, the table consists of two Colum Labels (Expense 1 and Expense 2). Values under the Column Labels are Actual Expense, Budget and Full Year budget. All Values (Actual Expense, Budget and Full Year budget) are totaled in the Grand Total. However, Management want to see Full Year Budget only in Grand Total (not under Expenses 1 and Expense 2). I was manually hiding those values. Is there a way to automate this? Thanks ...

'Turning on' automatic name checking and completion??
Does anyone know how to get this function to work. I see alot of info on how to disable it, but nothing on how to get it to work. In the Advanced Email options I have these to items checked: 1) Automatic Name Checking 2) Suggest names while completing To, CC, and BCC F fields. But when I start typing a name or email address it does not search. I have used the 'detect & repair' option on outlook and tried it on a clean outlook install and still nothing. Any Suggestions? Sounds like normal behavior to me. "Suggest Names" works while you type, but only uses data ...

Database table relationships
In visio 2007 I can not connect the relationship heads and tails to tables - Any suggestions? Thanks ...