I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al
Each table currentnly has 3 columns.
I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused:
Thanks
I coulnt figure out how to reply to a reply. This is a follow up question to a previous post.
I can now create a new column in my access db through my web form.
Here is the script: ALTER TABLE FAQ ADD City varchar(30)
this adds a new column called city. Here is my problem: Every time a user asks a question, that question will be in the new column created. I need the newly created columns to have unique names. I tried creating a form with a text file (to simulate where they type in their question) then post to post.asp. post.asp holds the script. I replaced city with ::T1:: (the variable of the text file), but got an error.
Creating columns dynamically is worthless unless there is a way to make the column names unique.
Can anyone shed some light on this problem for me?
I have an Access table with data fields however at after the last field column, I have about 15 blank and unselectable or uneditable columns that I don't want there. How I delete these? Reason I want to delete them is because table is a subform on a main form and these extra columns are visible on it and they also cause a scroll bar to shown
Table B contains columns like 1FC, 2FC,...., 12 FC
standing for forecast sale quantities.
Now I want the user to decide how many FC columns he wants, always starting with 1FC.Let's say he wants three FC columns.Now I want to map these 3 FC columns to the table A. Both tables contain column Product_Classes.Can I shrink table B to the 3 FC columns ?
I need to extract the value only for few columns dynamically from result set. I have written following code for the same:
Code: i = 0 strColumnsSQL = "Week" & CStr(gsReportingWeekNo) Do While i < 4 i = i + 1 strColumnsSQL = strColumnsSQL & ",Week" & CStr(gsReportingWeekNo) - i Loop
Customer Table > Customer ID (pk) 1 Issues Table > Customer ID (FK) Many Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
I am trying to add extra validation in a sample db (done in A97 (converted to A2003)see attached.
I am attempting the following: Please not that the db has existing validation which I would like to keep (or change if advised) ...
Conditions: 1. Input into field "ID" must start with the letter P or p or R or r 2. If field "ID" starts with the letter P or p and field "Code" starts with the letter R or r, then a message stating that this combination is invalid should appear 3. If field "ID" starts with the letter R or r and field "Code" starts with the letter P or p, then a message stating that this combination is invalid should appear.
I have a report with many columns in the detail listing data, and their associated titles above in the page header.What I would like to accomplish is the ability for the user to check boxes off on a form which will remove/add specific columns to the report. The form will essentially be a "build your report" style, with a button on it opening the print preview of the custom report.
Im assuming this is accomplished by the "on load" part of the form through VBA. How is the sizing handled? If the user un-check's a box to remove a column of data, how is the header and detail data "moved over" or re-arranged to accomplish a relatively decent looking layout? Any example or example code to deal with one column/setup (which I can copy and modified)??
I would like to add a page to a tab form when a certain button is clicked. I don't want to use the visible/hide solution. So I already found out that this can only be done in design view. I am using the following code:
Code:
Private Sub cmd_StrategyAdd_Click() DoCmd.OpenForm "Form1", acDesign Me.tabMain.Pages.Add DoCmd.OpenForm "Form1", acNormal End Sub
I am getting the error: Run-time error 2467. The expression you entered refers to an object that is closed or doesn't exist.I am sure the name of my tab form is tabMain.
I am using Access 2003 and have a custom shortcut menu. I wish to add some entries to this shortcut menu dynamically.
Background : I am displaying a list of customer orders and wish to offer the user the facility to filter the search for one item instead of the default of ALL.
The dynamic entries would be the unique order item reference nos. retrived for that particular customer retrieved via recordset . So the menu would show :
ALL ------ Model 1 Model 2 Model 3 etc. ...
How am I able to add these entries to the menu, the selection text will be the filter text I use to report upon.
In Excel, I can dynamically add option controls to a frame on a form using the Control.Add method. Is there an Access Equivalent? I can only find Count & Item as Control. items.
It might have something to do with design view i guess.
Hi..I have three tables A ,B and C, which have fields A,B and C respectively. I want to do that following default calulation
A.a=B.b+C.c meaning adding two columns of different tables and storing it in a third column. It works for me in the forms but I want to do that in the table. Is there any way to go about it
Background info: I have created a database that consists of 22 tables that contains hundereds of electronic components seperated by type. In each table are columns for evey project so that i can determine what projects each component is used in.
Everytime there is a new project i need to add the new column to every table. Is there a way to add the column to every table in one step rather than having to do it seperately for each table? Every table set up the same, same number of columns and column titles and everything.
I have a query that gives me a table with one column, say column A. I need to add 2 columns to the table and the coulmns will have fixed value. So I need to add column B and column C to my table and all rows in column B will have a fixed value and all rows in column C will have a fixed value.
What I have: Table with one column Column A a b c
What I need: Table with 3 columns: ColumnA Column B Column C a 12 14 b 12 14 c 12 14
when i am creating a query and need to be adding up all the data in the field, such as with the price needed to be paid overall the items, what do i have to do to create a part of the query or report to do this automatically for me? basically its a huge column of numbers that need to be simply added together and a grand total taken. please someone help. cheers.
when i am creating a query and need to be adding up all the data in the field, such as with the price needed to be paid overall the items, what do i have to do to create a part of the query or report to do this automatically for me? basically its a huge column of numbers that need to be simply added together and a grand total taken. please someone help. cheers.
I have to add the row data in two columns together. I used
val([COLUMN 1])+val([COLUMN 2]) and it works unless one of the columns has a zero in it. For example if column 1 is 25 and column 2 is NULL it runs the query but puts a #ERROR in those rows. Please help.
Hi guys, Excuse my novice questions. I have a query in which i did two things: defined a Column "Line Item Cost " as Sum ( using the dropdown in Design Grid of the query). Then i calculated another field: Expr13 which is: Expr 13: Sum([Line Item Cost]+ [Change Request Total Cost])
So, If Line Item Cost = 1363000 and Change Request Total Cost = -40000, then Expr13 : 1363000-40000 = 1323000, but i am getting the values: 1283000(Somehow the value is getting doubled before addition)
Please look at the attached Document. Please Help!
Hey, How can I add an aggregated column to an existing query? For example: I have 3 fields (a, b, c), each holding numbers. I want to add a fourth field which will average or use any other aggregated function on the 3 fields (but not by doing it manually).
I have a sharepoint list linked into my access database and every time when I work with queries and try to add up them up it causes the information to display in a text string. What to do in order to resolve this issue. For example, instead of $1,000+$5,000=$6,000 it will show 1,0005,000 as one string but not add them up.
I am extremely new to access. I am trying to add a combo box to one of my columns. Basically i have about ten columns of which a couple of them I want to have combo boxes so that the information that is in each of these columns is standardized. Currently all I have is my ten columns with headings set out.
My database has three tables with many columns. The three tables are identical in the names of their columns.
I want to copy all columns from all three tables together into one single table, giving the respective columns prefix table1-, table2-, table3- since the columns would otherwise be indistinguishable.
I already tried to search the board for "table columns add prefix".
I use Access 2010
I managed to copy all columns together into one table through design-view, but cannot figure out the "add prefix" step.
