Query Columns Renaming Themselves??

Nov 23, 2007

Hi all,

This is a strange one. I have a VERY simple query that runs directly off a single table which contains about 7 fields and one PK. I am pulling through 4 columns from this table including the primary key. I am renaming the columns using the format "NewName: Record_Id" format. I have also tried changing the caption on the query column properties.
Basically, the problem is that I save and close the working query but when I either close the database down or make a change to another form or query, the column names all get changed to a single value of the table, currently "Report Title". When the query is run, all the columns are shown with the same header name and the data results are all the same.
I have checked my relationships and they are fine and I have also done a compact/repair but without any luck. Can anyone please help me?

Thank you.

Gareth

View Replies


ADVERTISEMENT

Modules & VBA :: Union All Query - Transposing Columns To Rows With Variable Columns?

Aug 8, 2013

I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.

Example:

original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5

original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22

The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).

When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".

Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.

View 2 Replies View Related

Renaming Issue

Jun 20, 2005

Hi,

In Access 2003 when you rename a table any queries that the table is in will be renamed aswell, which is usually very useful.

However, is there a way to turn this property off, because I am trying to run over a hundred of the same query that use different tables? In Access 2000 I renamed the tables and used the same query, because it is much quicker, but I have just noticed that the data I am extracting is inaccurate, because when I undo the rename, the table names change in the query.

I will be very grateful for any help.

All the best,


Roly

View 1 Replies View Related

Renaming A Field

May 13, 2006

I have table with lot of fields, I would like to rename those fields, how do I do that using code or a query?

View 1 Replies View Related

Renaming Controls, Macros, Etc.

Jun 5, 2006

I have built a fairly large access database (office 2003) and unfortunately I was a bit careless about naming my controls and macros. I would like to go back over all of my form controls and macros so that they have more descriptive names, but I'm afraid this will break alot of dependencies. How can I have the macros autoupdate to compensate for renamed controls and vice versa?

View 2 Replies View Related

Renaming Table Names

Jan 4, 2008

Hellow,

Little question, hopefully not a big answer :)

i have four oracle db'ses mydb_db_test, mydb_db_production, mydb_db_develop

within access i have linked tables from the first one; let's say: mydb_db_test.tblOne, mydb_db_test.tblTwo

What i want to do is:
1) import the same tables from the other trhee db'ses
2) rename the table names by vba code
example: my_db_db_test.tblOne has to become tblOne
and when i switch from db (to for example the production), the tblOne has to be renamed originally (mydb_db_test.tblOne) and the other has to be renamed (so mydb_db_production.tblOne becomes tblOne).

Now the question which you probably ask me:
Why not by a connection string change...

Well, that's the problem, the linked tables are being set to readonly, and the property cannot be changed (at least, as far as i tried); so that's why i thought of this workaround. By linking all three databases, i also always have for those table the three connection strings, and by renaming them (i now do this manually) i always can pick the right connection.

Thanks in advance for the advise!

View 3 Replies View Related

Renaming A Filed In A Table

Jan 4, 2007

I have a table Client-Details,in which i have a record namely "bharti-chennai".this table is associated with aother table "release-details". bharti-chennai has records in relase details.i want to change the name of bharti-chennai to bharti -mumbai ..how to do it.

View 2 Replies View Related

Renaming PDF Report In Email?

Jul 14, 2015

I am trying to email a report and rename the report after the referenced NCR# currently selected on my form. My codes works for putting the referenced NCR# in my email subject, but I am having issues renaming the report and the error is with the SetProperty line bolded below. When I click my email button, I get Run-time error 32004: The control name "Supplier Chargebacks" is misspelled or refers to a control that doesn't exists.

Private Sub Command587_Click()
Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim NCRNum As String
NCRNum = Forms![NCR Input Form]![NCR #]

[Code] .....

View 5 Replies View Related

Modules & VBA :: Renaming Tables With Spaces In Name

Sep 30, 2013

I am developing code to trawl through the tables in a large number of databases that I am working on for a client and rename tables that have spaces in the name.

I tried using the following but get a 7874 error when the new table cannot be found.

DoCmd.Rename strExistingName, acTable, strNewName

I then tried copying the table using the following but get the same error message.

DoCmd.CopyObject, strExistingName, acTable, strNewName

What I need to do to rename a table with spaces in it?

View 3 Replies View Related

Queries :: Renaming Fields From One Table Value To Another

Mar 6, 2015

I want to automate a find and replace process to standardize names in a table, using the value from one table to replace another. The code I have is not quite working.

Background:
- Table [Checking] field [Description] has the source text field that I like to change/standardize
- Table [Rename] field [Description] has the text string used to search the [Checking].[Description] values. The search should contain wildcard logic so e.g. a [Rename].[Description] value of "Mobil" would find a [Checking].[Description] value of "Mobil 123", or "Mobil 234", or "Mobil123456 AB".
- Table [Rename] field [NewDescription] is the new value used. If [NewDescription] = "Mobilx", then the "Mobil 123", or "Mobil 234", or "Mobil123456 AB" would all be changed to "Mobilx".

I have the following code which partially works:

UPDATE checking, rename SET [Checking].[Description] = [Rename].[NewDescription]
WHERE ((([Checking].[Description]) like "*" & [Rename].[Description] &"*") );

Problem is the original [Checking].[Description] value is not deleted in full so the replacement [Checking].[NewDescription] value is not correct.

e.g. "Mobil12345 AB" might be changed to "Mobilx AB" instead of "Mobilx" (some of the original string remains).

View 8 Replies View Related

Renaming Crosstab Column Heading?

Mar 23, 2013

I have a cross tab query which give result like this

Date No. BoxItem1 BoxItem2 Item1 Item2
1-2-12 1 15 15 10 15
1-2-12 2 10 10
1-2-12 1 15 15
1-2-12 1 10 5 5 1

I need to BoxItem1 come after item1 and also col heading only box.Is it possible or not?

View 1 Replies View Related

Forms :: Get Date Create In Renaming Table

Jul 18, 2014

All, using access 2010. I have some code to rename a table in my database. Partial Code:

Code:
TableDefs("tblMstr").Name = "tblMstr" & Format(Date, "yyyymmdd")

This works but how do I use "datecreated" to get the creation date of the table and format it. I tried to use DateCreated in place of Date but get errors such as variable not defined or .datecreated without a with block.The result I'm going for is something like "tblMstr20140603".

View 5 Replies View Related

Renaming Column Headers In Pivot Table

Oct 24, 2014

I am in the process of developing a pivot table with grouping per month per year which works just fine. However, I would like to rename the column header items. It will not allow me.

The scenario:
the data is from a query that deprives data from two tables (Date from Table A) and (TypeID from Table B: Query will show TypeID in text format based on SELECT to show data in text format not numeric format).

The struggle:
Renaming the column headers which shows the TypeID in numbers based on its ID (the first column of Table B), not its Description (the second column of the Table B).

The question:
How do I make the column headers to show the TypeID in text based on its Description in the second column of the Table B?

View 1 Replies View Related

Modules & VBA :: Renaming Files Based On Records In A Table

Oct 16, 2014

Code to rename photos (Access 2010). The new name (full path) is listed on a table (although I would love to accomplish the task from the list query that I later used to generate the table). There is one field in the table (or query) called OldPath and one field called NewPath. The table name is RenPaths. Both paths are located in the same drive. I want to rename the file in a new folder. I created the new folder which is contained in the NewPath.

I was trying to accomplish this using the Name function

Name OldPath as NewPath

Here is the full code:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim OldPath As String
Dim NewPath As String
OldPath = "Select OldPath From RenPaths"

[Code] ....

Here is an example of the paths in my table:

Old Path is - C:UserskcrespoDocumentsFulcrum to MACPFulcrum_Export_a97dee97-ba92-455e-9d5c-3b35617ad357SAA Inspection Form367e14e0-439b-4a50-99e1-9154bcc9e3f7.jpg

New Path is - C:UserskcrespoDocumentsFulcrum to MACPFulcrum_Export_a97dee97-ba92-455e-9d5c-3b35617ad357SAA Inspection FormimageswwMH51856_A3f7.jpg

Im getting an error: Run time error '53': File not found

I checked the OldPath and everything is correct so if the code is working correctly it should have found the file.

View 3 Replies View Related

Modules & VBA :: Copying / Renaming - File Not Found Error

Oct 28, 2014

I haven't used FSO before, and it seems that the syntax is a little different than typical VBA for strings, but I can't quite figure out what I'm doing wrong here... I keep getting a "file not found" error on the "FSO.CopyFile..." line. I have printed all of my strings to the immediate window to check that they're printing correctly. It all looks good. My code is as follows:

'Copy file
'Set up strings for coding file names
Dim FilePath As String
FilePath = Me.txt_FilePath
Dim FileName As String
FileName = Me.txt_FileName

[Code] ....

View 9 Replies View Related

General :: Stop User Renaming ACCDR To ACCDB

Oct 14, 2013

I have a database that is saved as an accdr so it can be compiled for distribution as an exe..I have just found the accdr the exe installed and renamed to accrb and was then able to open it and view all the tables, forms etc.Obviously I cant stop someone renaming but if there was a way to stop the forms, tables etc opening.

View 8 Replies View Related

General :: Renaming Tables / Queries Using Navigation Pane

Feb 6, 2014

I understand the principle of renaming the above of using the navigation pane and selecting the object but what is happening in my case is that when I select the specific table/query I wish to rename, the text is highlighted as per normal but the instant I move the cursor onto that cell that whole panel highlights in red and then reverts back to the original state of just the text in that panel (now highlighted).

I do not know if this is related to the problem but I have a text box with the "code" =Count([tblEntries].[CageNo]).

This was working perfectly okay but both this and the renaming appeared to happen about the same time. I have opened other Access files and the same re the renaming occurs. I have secure security running and do not think this could be the problem but anything is possible.

View 7 Replies View Related

Queries :: Consolidate Multiple Columns Into Two Columns

May 14, 2014

I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.

What the simple query looks like: [URL] ...

Second what I want the query to look like: [URL] ....

View 2 Replies View Related

Sum Two Columns With Query

Jul 15, 2007

Hi,

I'm a newbie to Access. I have created a query showing the result base on other calculation. The returned results are in numeric. I would like to know how to sum up the returned results on row to a new column.

A b new column
100 200 300
200 150 350

Your kind help will be highly appreciated

View 9 Replies View Related

Colored Columns After A Query ?

Aug 23, 2006

Hi everyone....

I have imported a data sheet into Access that had certain columns a different color...green...they were colored so that the end user would
know which columns were to have input. (visually)
Is there anyway that I can keep this format or color the columns in Access?
Also the end result is from a Query...does that make a difference ?

Thanks....

View 2 Replies View Related

Max Columns/Fields In A Query

Nov 28, 2007

Could anyone tell me if there's a hardcoded maximum number of fields/columns in a query?

I ask, because through some experimentation and the urging of others on the boards, I've finally taken a look at the SQL view of a database and found it rather easy to understand. There, I found I had more flexibility filtering a Query via SQL in comparison to the query design window. (unlimited "Or" vs. 8).

So, even though there's a definite number of columns in the Query Design window, can I ignore than and just enter what I need in the SQL view?

View 6 Replies View Related

Month Columns In Query

Dec 17, 2007

Hi folks.

I've been looking for an easy way to have 12 columns in my query which are each of the 12 months and are all using the same date field "dateSent". So i'd want to just use the month part. within the actual columns there will be a disbursal field which will tell a user how much money has been sent (if any) on that month. then If done correclty, it could be analysed in Excel.

I've attached an example of what is required of me. I'm expected to have the same format but i'm unsure how I'm going to go about this.

I've attached an example. Maybe someone might know a good way to do this and perhaps point me in the right direction

I appreciate all help

Thanks.

View 5 Replies View Related

Sum Query Based Off Two Columns

Apr 15, 2008

I have a problem trying to sum a numeric column based off another column in the same table.
What I am trying to accomplish is to sum a numeric column if a corresponding column is Not Null.

I have uploaded my sample DB that to illustrate my problem. The query I am testing with is called Query1 and I am looking at summing point values from the task table grouped by the test case those tasks are in. I can easily sum the total number of points for each test case. But when I add another field to the query to sum the point values of the task in the test case that have a date entered into its corresponding date field it will not sum them correctly.
The result I get is the same sum total is shown for all three fields in the query.

I have tried several attempts at making criteria for this to work but nothing seems to work the way I want it too.

Here are my attempts:
IIf(Not (Sum([Task].[Points]))=IsNull("Attempted_Actual"),Sum([Task].[Points]),0)

IIf(Not (Sum([Task].[Points]))=IsNull("Completed_Actual"),Sum([Task].[Points]),0)

Not IsNull("Attempted_Actual")

Not IsNull("Completed_Actual")

My question is:

Is it possible to do want I want all in one select query or would this be something to code in a VBA module and then just call that module in a report?

My plan was to do this all in a query then build a report to display this query.

I have attached my dummyDB file and you can look at the Test_Case Table and expand each test case to see the data I have populated.

Any guidance would be appreciated.

Thanks,

View 4 Replies View Related

Add 2 Columns (counts) Together In A Query.

Mar 14, 2008

Hi. I am trying to do a query on a table that has 3 columns
ID, Person1, Person2

I want to query the data to find out how many rows each person's name appears in either Person1 or Person2.

I don't need seperate counts for when each name appears in Person1 or Person2, just the total for each person.

Result example that I'm looking for:
If table contained:
1 Eddie Sam
2 Pete Max
3 Sam Pete
4 Dave Eddie

Query to look like:
PersonsName Total
Eddie 2
Sam 2
Pete 2
Max 1
Dave 1

Surely this is easy to do?

Anyone? Thanks. Martin

View 6 Replies View Related

Queries :: Run A Query With Several Columns

Jan 3, 2014

I run a query with several columns. The first column in that query is full of individual names; about 50. I have another spreadsheet with three names. I used to be able to set a criteria or a filter (whatever you want to call it) in a query that when "Run" would only return to me the names from the spreadsheet with 3. Not a drop box or a pop up where I have to fill out the name, just a spreadsheet with all the transactions that occurred with these specific three individuals.

View 1 Replies View Related

How To Add Empty Columns To Tble Using Query?

Sep 29, 2006

I have a text file that I will be importing into a new table once a week (every week) that I will need to add about 30 more columns to before I export it to a new text file in preparation for importing it into another Access application.

Is there a way through a query to add the columns without having to open the table and manually add them each week?

Thanks,
David

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved