Im running a query and normally there is only a field-name in heading. I have
multiple tables with equal field names. Now I want to get table names in
heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames...
Im finding a solution. Help me please. Thanks :eek:
I have purposely used "False" to ensure that the first line in my spreadsheet is ignored. This is because the first line in my spreadsheet contains headings that do not match the column names in my table.
I do not wish to change my headings as end users will be making use of my application and they will not like headings such as "int_FactoryID". Likewise I do not want to change the column names in my table to words such as "Factory ID" as this would be a bad naming convention.
Is there a way to use TransferSpreadsheet without necessarily matching the headings in the spreadsheet to the column headings?
Is there a way for TransferSpreadsheet to ignore the headings and assume that the first column in the spreadsheet needs to go to the first column in my SQL Server table?
I have got a query which gives me the following output;
Nr ----------- area ------- area 2 ---------- holler 14-1096-------1------------1-----------------5.9 14-1097-------2------------2-----------------7.8 14-1100-------1------------1-----------------13.4 14-1101-------2------------2-----------------7.8
What i would like to do is to calculate the sum of holler when they are in the same area.So the sum of nr 14-1096 + 14-1100 and 14-1097 + 14-1101. Ive tried to do the following;I tried to do the following just to check it would even work;
Code: test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=("1")))
Which worked perfectly, it gave me 19.3..
Code: test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=(querytoetsn2hr_gemiddelde_filter.area2)))
That gave me the sum of all 4 the Nrs. Which makes sense, because you basically say that as long as area and area are the same calculate the sum of holler.if there is a way to say "sum of holler when area has the same value".
i want this to be editable. how would i do this using access form, or do i have to wrtite VBA code
PLUS i want the form to automatially have new records when i add for example a student, with ID 10011 OR if i add new records for a different date say 11/11/2006, then i want that to be viewd on the form just like the 21/09/2006 and the 04/11/2006.
please help :) been looking for a solution for long time.
Hey guys, I am trying to input data into a form, the form consist of mulitiple tables. Once I set it up I get a "#Name?" error in the text box, and when I try to input data into the text box, at the bottom of the form it keeps saying "control can't be edited; it's bound to unknown field 'FieldName'."
I have a report named rpt100 with two subreports srpt100a and srpt100b. The subreports are based on query qry100a and qry100b. Both queries are based on tbl100. I removed a field named 'Comment' from tbl100, as it wasn't useful; Also removed the fieldname from both qry100a and qry100b. When opening rpt100 a parameter dialog opens asking for data on the deleted fieldname 'Comment'. The field 'Comment' was never used in the report or subreports.
Inspection of the subreport fieldlist shows field 'Comment' still present.
How, other than remaking the rpt100 and both srpt100a and srpt100b, do I remove the field 'Comment'?
As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.
Based on each items introduction year, I want to list the first 5 years of sales.
I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )
Any help is appreciated!:)
(Note: I can't transpose the data in the tables for other reason)
Hi, Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies). Once these have been made, would like to open the query up, which I can do, then modify both the fieldname and the field criteria to suit my needs from parameters set in the form.
I have created a combo box search for my form based on three categories, 'Student Name', 'Nationality', 'Age' using the wizard. When I click on my combo box in form view, I see 'Alex', 'UK', '19' and 'Stephen', 'Sweden', '22' in the dropdown list, but I do not see the headings 'Student Name', 'Nationality', 'Age' as the first item on the list.
Not able to add more column heading in cross tab query. I tried to change the query properties to add more column headings as given below. In the query's Design view, right-click up in the area where your tables are shown and choose "Properties" from the right-click menu. The 3rd line down is for Column names. Enter what you need there.
Evn after doing it. i am not able to . Whn I try to save or view the datasheet it says. to create a crosstab query u need to have one or more row headin one column headin and one value.
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
I have a field in a query that contains numbers and text (text field). The numbers displayed come from a percent calculation and display with many decimals ie, .99898745987245. Is there a way to eliminate the decimals with code in the query field? For example .99898745987245 to equal 99%? I can’t format the field as a number or percent because it has both text and numbers. HELP!! Thanks
DB Setup: Table1: I have a table (Vendor) that has 2 fields (# & Name) with # being an AutoNumber. So only Name is being input via a form. I have formatted the autonumber field as 000;(000). Table2: A table that is populated via form with invoice info etc and vendor number is added through a drop down combo box (which also has the above format on it) Table3: Similar to table2, with slightly diff info but still vendor #
Query1: Is a make table that consolidates table 1 & 2 via union on like fields (ie vendor #) This make table also has the format from above in its properties field, although when i open the table it makes (Table4) the vendor field is not formatted as i need it. So 3 appears as 3 not 003.
Query2: takes table4 adds some extra info and exports file (as txt or xls)using outputTo & TransferText macro so that it can be loaded into a Hyperion Essbase system
My problem is that although the field value is formatted as 003 in appearance, when i take it to excel it changes back to 3 when i need it to stay as 003. I would like the make table query to also format the tables field as 000. Is the problem with the autonumber in the orig table or is it simply excel being stubborn when i take it there. If i changed the vendor field to text string in the make table would i still be able to link it back to the orig vendor table to get the names etc (ie number field linked to text field??) Thanks in advance
Please would someone be able to help me? I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format '00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
I have numerous databases that I use with make tables in there, these will often contain Currency values that we need to be set as just General Numbers. We can get it to work in the Query but whenever we run the query, the table it makes always shows up as currency.
Is there a way so that the table created will automatically be just General numbers...
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
I ahve in error typed the above - however it compiles and compacts and repairs without throwing an error. Should this be spotted before I actually run the line of code??
I am trying to create a query that can be customised by a combobox.
I have a combobox that lists the fieldnames from table (rather than records)
I want to be able to run a query that updates the field selected from combobox with the vaule from another txtBox
What I want to be able to write in SQL is;
UPDATE Products SET [Forms]![Master_Form]![Combobox] = [Forms]![Master_Form]![Qty] WHERE (((Products.ProductName)=[Forms]![Master_Form]![ProductName]));
