Why is my query limiting itself to only 16 columns? when I tab right for another field, it jumps back to the first field. I have other queries with far more columns than that. My DB size is 3,400 kb.
View RepliesI have two queries combined into one query. The first query shows a sales summary by part number. No problem so far. The second query shows inventory by part number. No problem with that.
Now, I combine the queries, and get only those results that appear in BOTH queries. In other words, if there is no inventory, I do not get zero in the inventory column, the entire result for that part number is skipped.
How do I still display the results of the first query if there is no match in the second query?
I am a very new access 2003 user and would welcome advice on how to cause the parameter text within [xxxxx...etc]to occupy two lines ( i.e force a carriage return and line feed) and thereby reduce the width of the parameter input box that the user sees. Also are there limits to the number of characters that can be used. I seem to remember a figure of 50 but cannot find it again in any of the books I have.
Many Thanks
Gerry
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
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 RelatedI have two calculated columns in my query for example... table.num1 - table.num2 as "Col1" and table.num1 - table.num3 as "Col2"
I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)
but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.
doing something like:
Col3: Col1 - Col2
doesnt work because it doesnt see Col1 and Col2 within the tables.
I have several tables that I need to join together to create a single form for multiple entries. Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.
Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly. The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.
This is what I attempted to use in SQL:
SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices]
FROM [Program List Table]
UNION
SELECT [Log Date], [Log Entry]
FROM [Program Notes]
However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.
I have a table with 4 columns :
Product Price Quantity Supplier
Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A
Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
tblStudents
studentID
studentName
studentEmail
tblResults
resultID
resultStudent
resultDate
resultTest
resultScore
tblTests
testID
testName
testPassScore
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.
View 13 Replies View RelatedStudent ID
Command over subject
Teaching Subject
Explaining things
Pedagogy Methods
Solicited_Participation_Class
1
Good
Average
Bad
Good
Bad
2
Bad
Average
Good
Bad
Good
3
[code]......
I have a table given above, table name is student. Which has following sample data.
I want to count the no. of students who say Good, Average ,Bad for every indicator Output from query:
Good
Average
Bad
Command over subject
2
3
1
Teaching Subject
1
5
0
[code]......
How this can be achived from query in MS Access 2010
I have a cross tab query. Essentially it groups together posted volumes into week numbers for different offices.
However, when I run the query, the order of the columns is not in a logical number order. I get Week 1 then Week 10 then Week 11 and Week 2 is further down the list and then Week 20 comes after that.
I would like if at all possible the Week Numbers to follow after one another i.e. Week 1 first then up to Week 52 in correct number order.
In my Dates Table I do have a SortID column which I hoped would resolve this issue so I could sort on the SortID column however this fails to work.
Attached is the query...
Code:
PARAMETERS [Forms]![frmSumOfVolByCCAndFormat]![cmbOfficeSearch] Text ( 255 ), Forms![frmSumOfVolByCCAndFormat]![txtStartDate] DateTime, Forms![frmSumOfVolByCCAndFormat]![txtEndDate] DateTime;
TRANSFORM Sum(tblTrafficEast.TrafficVolume) AS SumOfTrafficVolume
SELECT tblOffice.CostCentre, tblOffice.OfficeName, tblTrafficFormat.Format, Sum(tblTrafficEast.TrafficVolume) AS [Total Volume]
[Code] ....
The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).
I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
Code:
Origin Code Weight
Edison 13 4.25
Edison 13c 2.87
Piscataway 10 5.45
Middlesex 23 1.24
Edison 13 5.21
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
Code:
Origin 10 13 13c 23 25 27 27a other
Edison 9.46 2.87
Piscataway 5.45
Middlesex 1.24
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
I've set up a simple query that returns 6 columns of data.This query then shows on a sub-form elsewhere in my DB.The problem is that the query always appears with a horizontal slider. Allowing the user to scroll across to see the other columns in the query...The problem here is that there ARE no other columns of data. They are just empty. I want to restrict the output of this query to ONLY show the 6 columns that i have specified.
I have tried deleting the columns in Query Designer, then save the Query. But every time i re-open it half a dozen or so blank columns are stuck on the end.
I have the following query which returns 2 columns, where 2 fields are summed :-
Code:
SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices]
FROM Stats
WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));
This displays :-
345 - 988
How can I have the data displayed as
345
988
when I run the query.
I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.
The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".
Can any think of a solution or a potential work around to this?
I need to create a table with these columns in it.
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
I currently have a query that pulls selected data from a table. There can be multiple rows of data, and two columns include dollar amounts and quantities. I have a total line going at the bottom so I can see the Grant Total of all the rows (for dollar amounts and quantity).
Is it possible to add a column to this query that will calculate the expression:..?
=Grand Total of Dollar Amts for selected data/Grand Total of Quantity for selected data
I tried to use a query in a query, but must have done something wrong because it just said circular reference.
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] ....
Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.
View 4 Replies View RelatedI am making a classic sales over time crosstab query.
Rows: Customers
Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013
Is it possible to format the columns this way?
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
This is a query, report and vba question. I'm using Ms Access 2007.
TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate
I created a select query to join the info that I need.
SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))
I have 2 crosstab queries.
CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
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.