Queries :: Column Names Not In Table
May 15, 2015
I have 2 or 3 Queries which are run specifically export data to an Excel file required by external agencies.The queries contain column headings, required by the agencies, which are not derived from Table fields. Obviosly, no record data is produced for these columns.When the queries are run, the dreaded 'Enter Parameter Value' message appears for each to the additional column headings.
The following is a copy of the SQL Code for one such Query;
SELECT DISTINCTROW [Name1] AS Title, [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2] AS [Aggregated Donations], [Name3] AS [Sponsored Event], [Mail List].SubDate, Sum([Mail List].[ Recd]) AS [SumOf Recd]
FROM [Mail List]
GROUP BY [Name1], [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2], [Name3], [Mail List].SubDate, [Mail List].[Gift Aid]
HAVING ((([Mail List].SubDate)>#4/4/2014# And ([Mail List].SubDate)<#4/6/2015#) AND (([Mail List].[Gift Aid])=True));
Is there not some way in which these columns can be defined in Property Sheet such that this error message is prevented?
View Replies
ADVERTISEMENT
May 6, 2014
I am trying to create a rolling twelve month query that will total the months across. I set the query up so that it is a rolling twelve month, but I am having difficulty getting it to list the months across. I would like it to take the first month, list it as a column and show the totals according and the same with each month after. Here is the query that I currently have:
SELECT WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, IIf(Count(Switch([WIP_MONTH_NUM]='06',1))=1,Sum([WIP_AMOUNT]),' ') AS JUNE_TOTALS
FROM SpinsMonthEndTbl INNER JOIN WIPHistoryTbl ON (SpinsMonthEndTbl.SPINS_YEAR = WIPHistoryTbl.WIP_YEAR) AND (SpinsMonthEndTbl.ACCT_MONTH = WIPHistoryTbl.WIP_MONTH_NUM)
GROUP BY WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, WIPHistoryTbl.WIP_MONTH_NUM, SpinsMonthEndTbl.BEG_DATE, SpinsMonthEndTbl.END_DATE
HAVING (((SpinsMonthEndTbl.BEG_DATE) Between (Now()-365) And (Now())))
ORDER BY WIPHistoryTbl.WIP_YEAR, WIPHistoryTbl.WIP_MONTH_NUM;
I would really like it if the query could look at the BEG_DATE, select the corresponding ACCT_MONTH and show it as the column header.
These are what I would like my results to look like:
PART_NUM | 06_TOTALS | 07_TOTALS | 08_TOTALS . . . . . . . . .
123456789 | 10 | 5 | 15 .
View 2 Replies
View Related
Jul 7, 2005
Hi All,
How to get the column names of a table through a query ? Is it possible in Ms-Access ?
Thanks
View 1 Replies
View Related
Feb 21, 2007
Hi!
Can I do a select query in order to get all the column names from a certain table in access?
View 3 Replies
View Related
May 7, 2014
I have a spreadsheet which contains data exported from another system (which I have no control over). I'm using this spreadsheet as a linked table in my database.
The problem I am having is that I can't guarantee that when the spreadsheet is updated, it will contain the same column headings as it did the last time...
The spreadsheet contains a list of temps, with a summary of info off their submitted timesheets. So the column headings (as well as WorkerName, TimesheetDate, etc) may be "Standard Hour", "Overtime Hour", "Over 12 hrs Hour", "Standard Day", "Overtime Day", etc - for each type there will be a Pay Rate and a number of units (hours or days) claimed. The columns only appear if 'someone' in the spreadsheet has claimed something under that heading this week.
What I need to do is to produce a report which gives a summary by person and week of the number of hours claimed and the total charge. I've done that - that part was easy The part I'm struggling with, is how to take the column headings and turn those into descriptors for each charge type... in otherwords, to go from the sample 'timesheet' below to the sample 'ByType' ?
... when I don't know which columns will be present each week?
At the moment I'm using a union query to pull out the info I need, but if the column headings change then I know it will stop working...
sample of my union query... I currently have 8 different sets of bill rate and charge rate, this just does the first couple...
Code:
SELECT qryTimesheetBaseData.[Time Sheet Start Date], qryTimesheetBaseData.[Time Sheet End Date], qryTimesheetBaseData.[Cost Centre], qryTimesheetBaseData.Worker, "Standard" AS RateType, "Hourly" AS RateCategory, qryTimesheetBaseData.[Bill Rate (ST/Hr)] AS Rate, qryTimesheetBaseData.[Time Sheet Billable Hours (ST/Hr)] AS Billable, qryTimesheetBaseData.[Time sheet Status] AS Status
[Code] ....
View 3 Replies
View Related
Jul 28, 2014
I am using Access 2007 and would like to know how to copy column names from one table to another. I am importing data from excel and the column names come out as 'Field 1', 'Field 2', etc. I went through the trouble of manually renaming the column names of one of the tables (22 columns in all). I will be importing/creating 55 new access tables and don't want to have to manually rename all of these columns. All of the columns in all of the tables will be exactly the same, so I was hoping that after I did this once, I could copy the column names to the other 54 tables.
View 1 Replies
View Related
Aug 27, 2014
I'm tracking the holiday entitlement of a team of people. I use a query to work out how much unbooked holiday they have to take.
My problem is where I'm scheduling next year my query returns the names of those who have booked a holiday and their remaining entitelement. That's as it should be. However if someone hasn't yet booked any holidays then it simply doesn't display their record. I would like it to treat that record as zero and show the remaining entitlement as a full years entitlement.
Here's the SQL
SELECT Employees.Trainer_Name, Sum([2015 Holiday].[2015 Days]) AS [SumOf2015 Days], Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015
FROM [2015 Holiday] INNER JOIN Employees ON [2015 Holiday].Trainer_Name = Employees.Trainer_Name
GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;
The problem here is that the Sum of 2015 holiday is Null
Do I somehow need to create 0 hours records?
View 14 Replies
View Related
Feb 12, 2008
I have an Access table say Tbl_People that looks like :
ID1-ID2-Name-Age-Location
xxx-yyy-Mike-25-Essex
uuu-vvv-Jack-32-Surrey
mmm-nnn-Bob-36-Newcastle
I want to transfer this data into another table say Tbl_Output with four columns in the format below:
xxx-yyy-Name-Mike
xxx-yyy-Age-25
xxx-yyy-Location-Essex
uuu-vvv-Name-Jack
uuu-vvv-Age-32
uuu-vvv-Location-Surrey
mmm-nnn-Name-Bob
mmm-nnn-Age-36
mmm-nnn-Location-Newcastle
In Tbl_Output's 3rd column, only the Columns names: Name, Age and Location are repeated for each person and not column names ID1,ID2 (only its data xxx,yyy etc. is required in columns 1 and 2 as shown).
I was helped by rpeare with a VBA module that gives a single column output in Tbl_Output as
Mike
25
Essex
Jack
32
Surrey
Bob
36
Newcastle
The code is:
Sub main()
Dim db As Database
Dim rstElements As Recordset
Dim sName As String
Dim sNumber As String
Dim sArea As String
Dim freefile
Dim Filenumber As Integer
Dim sSQL As String
Set db = CurrentDb
Set rstElements = db.OpenRecordset("tbl_elements")
rstElements.MoveFirst
sSQL = "DELETE * FROM Tbl_Output"
db.Execute sSQL
Do While rstElements.EOF <> True
sName = rstElements.Fields(1)
sNumber = rstElements.Fields(2)
sArea = rstElements.Fields(3)
sSQL = "INSERT INTO Tbl_Output (OutputField) SELECT '" & sName & "'"
db.Execute sSQL
sSQL = "INSERT INTO Tbl_Output (OutputField) SELECT '" & sNumber & "'"
db.Execute sSQL
sSQL = "INSERT INTO Tbl_Output (OutputField) Select '" & sArea & "'"
db.Execute sSQL
rstElements.MoveNext
Loop
Set rstElements = Nothing
Set db = Nothing
End Sub
How can this be modified to get the required format data above? Thanks for any help in advance
View 4 Replies
View Related
Jul 31, 2014
My employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.
Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?
Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.
View 10 Replies
View Related
Dec 16, 2005
:mad: :mad:
Hi folks,
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
Thanks.
View 1 Replies
View Related
Jan 17, 2014
How can I repeat column names and row names on multiple pages of the report ?
View 1 Replies
View Related
Jul 29, 2013
I have a table with associate names and rankings for each month. I need some way to find out which associates appear in 2 consecutive months with a certain rating. I have a query that will allow you to key in which month you want to look at, but I can't figure out how to write the query to return results only for those associates that appear twice in the months chosen. For example:
John Doe was rated 1.5 in May and 3 in June.
Jane Doe was rated a 1.5 in May and 1.5 in June.
I want a query that will allow me to put in May and June as the criteria as well as < 2 for the rating and only return those that show in both months...
View 5 Replies
View Related
Aug 18, 2014
I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
View 9 Replies
View Related
Aug 27, 2014
I use the following code to delete from a table all records except those meeting the WHERE criteria:
Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));
I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).
Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?
View 5 Replies
View Related
May 21, 2013
I have a yes/no field as a book mark in a table with names. How can I query it to open to the last or the first check?
View 13 Replies
View Related
Sep 1, 2004
Hi,
I am trying to create an ASP script that I can use to take the column names out of my access database, and then take out the data. I have a select statment like this:
Code: <%Set conn = Server.CreateObject("ADODB.Connection")conn.open "source","",""SQL_query = "SELECT * FROM table"Set RS = conn.Execute(SQL_query)%><%WHILE NOT RS.EOF%><%=RS("field")%><%RS.MoveNext WEND%>
I would just like to make it so the above script just takes out each column header from my database table, and instead of the <%=RS("field") it shows the current column name.
Thank you for your help,
~David
View 5 Replies
View Related
Apr 4, 2005
Is there a way to run a query against a table that would return the column names, no data just the column names.
Thank you for any help.
View 5 Replies
View Related
Jun 2, 2005
Can I edit a column nameto show a different name than in my table as a heading?
View 2 Replies
View Related
Aug 31, 2005
Hi Everyone:
I have a project of migrating our Access data to Oracle. One of the pre-reqs the boss wants is to uppercase all of the column names (Field name) for all of the tables. I want to avoid manually going in all the tables and changing the case of the columns because there is like 300 tables where dealing with. I was wondering if in Access..is there an efficient way of converting my column names to upper case via script? VBA or Macro?
View 2 Replies
View Related
Mar 2, 2014
how can I use the column repmonth in the following statement?
repmonth is as a string declared and given by the user, in my case repmonth = 112013.
Code:
Public Sub TEST2()
DoCmd.RunSQL "SELECT PPC_REPORT.Reporting_Month, Report_Products.Division,
Report_Products.Product_Class, PPC_REPORT.Sales_Quantity,
Sales_QUANTITY_BP_2013.'" & repmonth & "' , Sales_QUANTITY_BP_2013.Total,
Sales_Quantity_'" & CF_PERIOD & "'.' & repmonth & "', Sales_Quantity_'" & CF_PERIOD
[Code] ......
View 3 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Sep 5, 2013
I want to be able to compare two tables to see whether their Column Names & sequence are the same.
View 1 Replies
View Related
Nov 29, 2011
Select Year,Type,Count(*) from Table group by Year,Type;
I am running the above query to create a bargraph:
Result of my query:
Year Type Count
---- ----- ------
OCt-10 Type1 5
Apr-11 Type 4 4
Expected graph:
Y axis Counts
X axis Year and then Type together
Two bars in total :
One bar for Oct-2010,Type 1 showing a count of 5
One bar for Apr-2011,Type 4 showing a count of 4
Is it possible to show Year as well as the Type values as labels in X axis in a barchart. I know it can be done with pivot charts but I dont know how to create it as a report with pivot barchart .
View 3 Replies
View Related
Feb 26, 2014
Is there a way of changing the column heading names on the split form portion of an access form. I tried changing them using a query to populate the form and changing the caption of each field in the query and that didn't work. I also tried adding a [Caption] at the design level of the table and that had no affect also. The split form seems to be displaying a portion of the actual field name or something like that.
View 3 Replies
View Related
Jan 10, 2014
I have a report in Access that produces variable headers as the output is different from client to client. When exporting to PDF everything works great but when exporting to Excel these variable headers are exported with their true names. Col8, Col9, Col10 etc. And our clients insist on Excel reports. Is there a way to get the assigned name to export to Excel? Assigned names would be something like Medical, Dental, Vision or whatever is applicable to the client and is assigned to the report through VBA code. BTW, the data for the report is pulled from a cross-tab query so exporting directly from the query has its own set of formatting issues and is the reason I am using a report with variable column headers.
View 2 Replies
View Related
Sep 18, 2014
I have a table "Service_Information" with 6 Columns like ID, Part_No, Part_Name, Description, Morning_Records, Evening_Records.
Up to Morning_Records, the User will populate the Data with User Form.
I would like to populate the Column "Evening_Records" from Other Table "Evening_Variables" where the Data Exists. like Vlookup in Excel. Is it possible to do with Query?
View 1 Replies
View Related
