I have a simple report which shows data form a table through a basic query and displays it just as the table view. I was wondering if there is a way to get this information to display in two columns on a page rather than just one list which takes up multiple pages?
View RepliesAccess 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
I can't seem to find a way to SUM two seperate columns on my report. I've attached an example of what I am talking about. I was able to add the SUM feature to the 'Estimate' column, but it doesn't allow me to add that to the 'Plan' column too. The only options available are Count Records or Count Value.how I can have these both SUM seperately within their columns?
View 5 Replies View RelatedI am using Access 2003 and trying to create a report that has a running balance of payments into an account. There are also payments out of the account which reduce the balance.
When I add the other columns into the expression for the running total the result shows in the report as "0".
The expression I am using is: =([Escrow Pmt Amount]-[Ins Pmt Amount])
I have this expression in the "Detail" section of the report with the "Running Total" toggled to "Accross Group".
How can I subtract the values in the other columns (Outgoing Payments) from the balance and continue the running total.
The list of payments within the report are date driven and grouped by an account number.
I have a report, a phone list, which has two columns, see the attached file for a screen shot.
The Employee Name, Mobile Phone and Desk Phone text is in the "Page Header". The Data fields that retrieve from the table are in the "Detail" section. The whole report is 8.89cm wide. The two columns fit perfectly across a portrait A4 sheet.
When I run the report to Print Preview, I get the two columns fine, but the Employee Name, Mobile Phone and Desk Phone text doesn't repeat at the top of the second column. Is there some way of achieving this? I want the part I have circled in RED to appear over the top of the second column as well.
I would have thought there would be a simple check box somehwere that would be something like : "Repeat Page Header on multiple columns", or something as equally useful. Strange that this is so difficult to do, as am sure that this sort of thing is something that lots of people would want to do?
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 relatively new to writing databases in access, I did some a long time ago, but cant remember what on earth I used to do.
I have created a database with all the information I need it and have a query set up that gives different results depending on the information the user has entered
The user decides what the query will display though a form.
I want access to generate a report with the information in it that the query chucks out.
However the information the query gives changes every time depending on the user input.
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
Thanks!! :D
Student 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
Hi,
I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.
I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.
For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...
So at the moment i have two queries per responsibility, and one pulling those two together i.e.:
New Work Items:
SELECT Count(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));
Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;
Is there anyway of combining the first two queries into one, rather than have 3??
Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....
Many Thanks!
Gareth
PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...
I have an access database which is going to present a front end webpage form. The form has several drop down lists and I need one of those drop down list to display only certain results dependent on what the previous drop down list has selected e.g. if Box A is selected as Aces then I need the next drop down box to only display A building
1-A building
2 A building
3 and not the entire list.
How can i get this to work?
I am using the query below
What I am trying to do is get 4 combinations
1) Department + source of Tag+ specific Date
2) Department + Specific Date
3) Source of Tag + Specific Date
4) All records
This query is meeting my 2nd and 3rd condition however if I delete the parameters for 2nd and 3rd 1st combination works fine. But I want all four conditions met in one query
SELECT [Unsafe Act Unsafe Condition].*, [Unsafe Act Unsafe Condition].Department,
[Unsafe Act Unsafe Condition].Date, [Unsafe Act Unsafe Condition].[Source Of Tag],
[Unsafe Act Unsafe Condition].[Close Date]
FROM [Unsafe Act Unsafe Condition]
WHERE ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmHighLevelReport]![cboDepartment])
[Code] .....
I have been all over the internet and trying different things for hours to no solution. I have created a form (Code) which I am using to auto fill two categories in a form using dlookup. Both categories fill perfectly but I cannot get the information to transfer to a query in order to capture the information in a report.
These are the dlookups I've been using and the categories I'm attempting to capture are "Description" and "Category"
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
I am new to Access and have been hitting a wall and seem to be finding nowhere that has this same type of task.
I have a table(Table 1 ) with 3 columns...
Table 1
I am trying to run a query that returns Table 1 but excludes rows that meet multiple criteria. I need to return rows that do NOT contain the combination of criteria below
Criteria
1) Lot Number - Left([Lot Number],2) = "PT"
2) Transaction Desc. - "Put-away"
Basically, i want exclude a "Lot number" starting with "PT" IN COMBINATION WITH a "Transaction Desc" of "Put-away". The goal is for the query to return ROW2 and ROW3 and exclude ROW1.
ROW1 would be excluded from the query result because it meets both criteria.
ROW2 and ROW3 would be included because it does not fully meet all criteria.
I enter personnel data into a table through a form. The same person may have multiple records in the table. With each record there are yes/no boxes to be checked if that person is verified at a certain station. Let's say PERSON A has six records in the table. Let's say on one of those records he has StaPrimary yes/no box checked (or true).
I need a query to do the following: My query is to find out if any person listed in the table does not have any StaPrimary yes/no boxes checked. My dilemma is when I do the query if Person A has six records and one of the records has the StaPrimary yes/no box checked his name will still show up in the query because he has other recrods with the StaPrimary yes/no box not checked. I want to check all of each persons records in the table and if they have one StaPrimary yes/no box checked I do not want there name to show up in the query. How do I do that?
Thanks for your help
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
I have researched on here how to print the results of a query to a text file. I put the code in and I get an error on the openrecordset line. The error says "too few parameters, expected 4"
I tried the query in another report I export to excel and the query works.
I tried printing the whole table using "Select * from tbl_Customers" and it works????
Here is the code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryBell1", dbOpenSnapshot)
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile(pathname, True)
[Code] ....
the query returns 6 fields for printing and can filter based on whether 3 fields are filled or not on the form - Date, Campaign, Status
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
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] ....
I have a queries that do all the calculations and dumps the output to Query X for all different types of customers. At the moment I am required to generate a report for each of the customers and send it to them, manually.
End Goal:Initiate a Macro (at a given time at a given frequency) that would run a process to generate different reports for all different types of customers using a standard report template. I am also trying to avoid having to create a report for each customer (as the customer base grows, the report count will grow) so looking at something that would look into Query X and generate # of reports depending on number of customers.
I currently have a combo box (combo121) on a form. I select the "Company" which is connected to "Company" in the query via [Forms]![courseinfo].[combo121]..That is working. When I press the "query" button on the form the selected company in the combo box is updated in the query and it updates the report templates.I also want to use the same system to populate a different report, using the same query. This works a bit differently.
Once the company is selected in the first combo box(combo121). I have other combo boxes which bring up students attached to that company. I have multiple combo boxes because I need select multiple students for the one report. These students are in fields "First Name" and "Last Name". So in the query I can only have those fields once. I have several links to the several combo boxes under "Last Name" as that is the "bound" selection for the combo box:
[Forms]![courseinfo].[comboname1]
[Forms]![courseinfo].[comboname2]
[Forms]![courseinfo].[comboname3]
Problem I am finding is this is bringing up several lines in the query. So when I try to populate the report with all of the different Students I have selected for the query, it won't work properly because I can only put "First Name" & 'Last Name" fields on the report once.
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
Lets say I have the follow Tables:
Code:
Outfits:
ID | Top Color ID | Bottom Color ID
1 2 1
2 3 4
Colors:
ID | Name
1 Red
2 Blue
3 Green
4 Orange
I'd like to have a query in design view to have the following result
Code:
Outfit ID | Top Color Name | Bottom Color Name
1 Blue Red
2 Green Orange
In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".
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.
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
See the attached file ....