Data From 3 Queries Into 1 Report
Apr 11, 2008
Hi all, let me start by saying that i am not that experienced with access but find it really enjoyable and want to learn more. i dont always understand or use the right "jargon" but here goes. i have a db with 3 main tables, each table has the same layout and info inputed into it (ie, name, address, product, ref no. etc.). from each table i can print a sales reciept with all of the relevent info on it. i also print a sheet (report) with the customer names & addresses on it, used as the postal labels. i currently print 3 postal label sheets, 1 for each table. what i would like to do is print just the one report but taking the name & address data from all 3 tables. would really, really, really, really appreciate any help on this as it is beginning to frustrate me, and waste a lot of paper !!.
i'm using access from microsoft office professional plus 2007.
many thanks
dbwannabe
View Replies
ADVERTISEMENT
Feb 8, 2015
I'm using Access 2010. I have a report that summarises students and the number of courses they are attending after a set date. My query lists name, course date, course description, the count being on the course description. I thought it was working until I noticed that students are listed twice if they attended courses on two separate days.
For example
Liz 4
Liz 3
instead of
Liz 7
I have moved the count to other fields but it then doesn't show any students at all when I run the query.
View 3 Replies
View Related
Feb 26, 2015
I created a query and one of the fields was "name". In the query it listed the names and then changed to the ID number of the names from the name table. The query was created using the wizard. Why did the query change to the ID number mid report and how do I get it to report only the names and not the ID number.
View 5 Replies
View Related
Apr 2, 2013
I am trying to create a form to enter data into a table that I ultimately will create a report from. I have created a blank table with the columns I need. I created an append query to add the new records and an update query and a macro to run them on click of a button. It all runs but it doesn't append anything to the table. What am I doing wrong?
View 6 Replies
View Related
Jul 12, 2013
I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
View 4 Replies
View Related
Dec 17, 2014
I create a query to bring data from a table and then print a report that display this data
I put a criteria on the cities field to filter this query. It will take the name of the city from a txtbox in a specific form
Everything is ok, but when i want to print all records with any city without filtering so the query. How can i do it
View 5 Replies
View Related
May 14, 2013
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
View 5 Replies
View Related
May 6, 2013
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
View 4 Replies
View Related
Apr 23, 2014
I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.
My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.
Is it possible to write a query from this table that will enable this?
View 4 Replies
View Related
Sep 4, 2013
I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.
Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.
I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"
Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;
I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...
View 5 Replies
View Related
Jul 31, 2013
I am looking to call two different queries from report wizard to produce report. Getting error message what to do in this situation as both queries are important as i have to pick all records from query A and just one record from query B any other option to get this in report.
View 4 Replies
View Related
Aug 13, 2015
I have a form with 7 List boxes linked to 7 Query's which in turn are linked to a table. Each list box if for a particular trade.
I am trying to select a person or persons from each List box and then have them sent to a report. I have Code to do one list box, but do not know how to link all boxes with code to a 'Open report' button.
The code I am using is as follows:-
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
[code]....
View 9 Replies
View Related
Dec 21, 2014
How do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
View 10 Replies
View Related
Dec 21, 2012
I have an asset data base to generate individual asset detail reports with a sub report on the same page listing similar assets from a separate query. I have set the master and child fields, one to many. This works perfectly for the first two assets (pages), however the sub report stops showing data on the third asset (page).
View 13 Replies
View Related
Sep 2, 2014
I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.
How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?
View 8 Replies
View Related
Mar 20, 2015
So I have a text box in a report that I want to pull in data from a field in another report, so the 'control' for my text box looks something like this...
=[Reports]![rptSalesReceiptSub]![Text141] (i.e Text141 is the data I want to pull into my report)
...the path is completely correct but when I run my report I get.#Name? If I run the report that has the data I want, it's fine (in other words Text141 has valid data in it)
View 4 Replies
View Related
Apr 16, 2014
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
View 3 Replies
View Related
Aug 10, 2012
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
View 14 Replies
View Related
Dec 31, 2012
I have a database with a form called "Main" where users input data and then print a report from it. "Main" has fields in it from another form "Members". This data (from "Members") is shown on "Main' by Dlookup coding, and therefore cannot be selected for input by the user. Now, lets say a user inputs data into "Main" and prints the report on 12/30/2012. On the next day, a member's name is changed and I update that data in the "Members" form. On 12/31, I would like to print the report again, but it shows the updated member's name instead of what is was like on 12/30. How can I keep the old data in case I want to print the report in the future like it was initially printed? What do I need to do to any form(s), report or what VBA code needs to be written?
View 3 Replies
View Related
Aug 9, 2013
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution
Query 2: Total Distribution
etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
View 1 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Oct 1, 2013
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?
View 3 Replies
View Related
Apr 22, 2013
I have a query called "Stock" containing field like (Item, Description, product_qty)
Another is a table called "Sales" with fields like (Sales_ID, Item, Sales_description, sales_Qty, date_Of_Sale) Item field on sales is a foreign key.
Now what i want is how can i make when the user enters new record, in field "Sales_Qty" the data entered here to be less than product_Qty.
NB; this is because you can not sell more than what you have.
View 4 Replies
View Related
Sep 7, 2013
Attached you will find an example with the problem.
The query is only searching identical data between both tables.
I want it to show results from all users, with all the devices each user has.
What am I doing wrong with the query?
View 4 Replies
View Related
Sep 30, 2014
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
View 4 Replies
View Related
Apr 14, 2014
I have a table (tbl1) contains sample records in Basecode column like S2378797 , R1165778 , W1165778 , N1165778
Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111
[code]....
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
Third query not working
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
View 2 Replies
View Related
