Extract Value Only For Few Columns Dynamically From Result Set
Aug 4, 2012
I have table with following Columns:
Banker
Status
Week1
Week2
Week3
Week4
......
Week48
Week49
Week50
Budget
YearMonth
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
[code]....
View Replies
ADVERTISEMENT
Mar 16, 2014
got the following issue:
There are two tables A und B.
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 ?
View 3 Replies
View Related
Mar 5, 2007
Hi
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
View 1 Replies
View Related
Nov 12, 2005
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?
Much appreciated
Mike
View 3 Replies
View Related
May 28, 2013
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)??
View 4 Replies
View Related
Mar 14, 2014
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
View 1 Replies
View Related
May 18, 2007
Hello,
In a form I have "txtBoxA". I would like store the number of records from table "A" based on a specific field into "txtBoxA".
Ex:
Table 'A' fields: QuoteID, Date, GenerateOrder
What would be the exact syntax for this??
The following code is not working
Me.txtBoxA.Value = DoCmd.RunSQL ("SELECT COUNT(GenerateOrder) FROM A")
Thanks
View 2 Replies
View Related
Sep 12, 2013
I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)
SCHOOL_YEAR (all the students that are):
1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year
Not to show dripouts, na and finished
Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)
View 3 Replies
View Related
Jun 24, 2014
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
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
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
May 29, 2007
All:
I'd like to make the result of my text box flash (in my form) if the its value is less than 30..For example. If A - B < 30, then the result whatever it is as long as it is less than 30..Is this possible in Access? thanks in advance.. :
View 1 Replies
View Related
Oct 30, 2007
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
View 4 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Feb 24, 2005
This may be a really straightforward one but I cannot seem to find a solution.
I need to run a select query, (for a mailout) then mark the records that have been selected in the query on the main database table with a mailshot code.
Any ideas on how best to achieve this?
I was thinking of making the select query maketable (because I have to export a txt file of email addresses for a mailing list), then add a mailshot code field, then append this extra field to the main table.
The trouble is that I am not sure how to use the query to add this extra field to the maketable & once this has been done, how do I append the values to the original table when the mailshot code field does not exist, can it be dynamically created?
Any help will be much appreciated.
View 11 Replies
View Related
Oct 1, 2005
Hi,
I have a subform called "Earthwork". I also have a query called earthwork200 and I need to dynamically transfer data from the earthwork to the query based on a certain column.
for example, the earthwork column i am using to transfer data to the query is "Desc" (the name of the column). How do i get data to dynamically goto the query as I click on a new item under the "desc" column?
Kindly HELP!!
View 1 Replies
View Related
Feb 18, 2006
Hi All,
I have built queries based on tables. The field names are the technical field names of an application. For example pernr is actually Personnel Number etc.
My queries are set up as follows:
Field = pernr
Table = etc
I would like to rename pernr to Personnel Number so that the column heading in the datasheet view is Personnel Number.
I know I can do this manually for each query by using the following as the field: Personnel Number: pernr
Is there any way of getting this be done dynamically using a lookup table etc. I have lots of queries and I don't want to manually change them all. Furthermore, if I wanted to rename Personnel Number to Personnel No. then I could do it in the mapping table rather than in all the queries.
Thanks,
Evan
View 2 Replies
View Related
May 23, 2006
Hi Guys
my database is essentially a questionnaire of around 200 questions. I'm trying to add a 'light' version to it.
I've added a 'include in light verion' tick-box to all the questions and a 'light analysis' tick box on the registration page.
The questions are all genertated via queries pulling out the appropriate ones as required. What I'm tying to do is add to these queries so that if the 'perfomr light analysis' tick box is ticked only those questions that have been indicated as light are included, and if it's not all teh questions are included.
I can do either-or but not both dynamically
Can anybody help?
Thanks
scube
View 1 Replies
View Related
Dec 6, 2006
I don't know if anyone can help me with this one at all.
I have a query that contains all the possible data that my users will wish to get data from but I want them to have the option to show/hide particular columns to make the query ad-hoc.
I have some code that copies my query to another of a unique name during the course of their Access session so they end up with a query with the name 'ABC' for example that they can run.
If this query ABC has columns A,B,C,D which are all defaulted to be shown and the user decides they are not interested in columns B and C is there Access code that is equivilent to this pseudo-code?
ABC.A.Show = True
ABC.B.Show = False
ABC.C.Show = False
ABC.D.Show = True
where ABC is a query, A..D are columns and Show is the checkbox in the query definition.
That I can run in VB 6.3?
Aenathras
View 3 Replies
View Related
Jun 1, 2005
Hello Guys,
I have proj name m1.mdb which has forms and reports.I have 2 forms form1 ,form2.In form2 I have button that creates new database named m2_date.mdb and creates report and form1 by using property DoCmd.TransferDatabase acExport, "Microsoft Access", sFilename, acForm, "form1", "form1", StructureOnly, StoreLogin,now this form1 has to be seen first when any user doubleclicks m2_user.mdb,so my question is, is there any way i can setup startup form properties dynamically ,without using startup form manually from tool properties of toolbar.
hope to get answer..
thanks
vsap
View 2 Replies
View Related
Dec 9, 2005
I have a form that has several rows of controls (comboboxes, textboxes, checkboxes, etc)
Each row is exactly the same. There is one row for every 'driver' in the 'SalesDrivers' Table.
Instead of manually adding a row when we add a new driver I would like to form to dynamically create the form based on the drivers in the table.
Any suggestions?
View 6 Replies
View Related
Mar 27, 2006
sorry if this q has already been asked, but i couldnt find what im looking for after searching..
i have a main form. there is a combo box that is that is bound to tblSupplierDetails. this combox displays the records stored in tblSupplierDetails fine. now beside this combobox i have a hyperlink that opens a subform called 'subfrmAddNewSuppliers'. this subform works correctly - ie. when details are entered here, they are stored in tblSupplierDetails
my prob is that if the user adds new supplier details via the subform, these details do not appear in the combo box for the user to select when they are returned to the main form.. they appear if the form is restarted
Ive inserted the statesment Me.ComboBoxName.Requery (with ComboBoxName ammended) into the event procedure for After Update for the combobox.
however, with this the new details which are added via the subform are replacing the previously added record - so theres only ever one record in tblSupplierDetails.. can someone pls help me with this?
View 7 Replies
View Related
May 8, 2006
I have a database that has a field [country] that stores the numerical id of the country from a table of countries. The student table also has a field called [mother tongue] and it seems to me that this is duplication.
I created a simple query based on a link table that tells me the country and its associated mother tongue.
SELECT [-LOOKUP-ALL-Country-LinkToMotherTongue].Country, [-LOOKUP-ALL-Country-LinkToMotherTongue].MotherTongueLinkNo, [-----LMotherTongue].Language
FROM [-----LMotherTongue] RIGHT JOIN [-LOOKUP-ALL-Country-LinkToMotherTongue] ON [-----LMotherTongue].[Reference no] = [-LOOKUP-ALL-Country-LinkToMotherTongue].MotherTongueLinkNo;
It seems to me to be an overhead to store in the student table the mother tongue id.
How can I create a text lable that dynamically shows the mothertongue as i move over records?
that way the user can see the students mother tongue but I only store the country table and link table to mother tongue. as opposed to a mother tongue entry against all students.
regards in advance
Peter
View 2 Replies
View Related
