Creating A Master Query Or Report From Multiple Queries
Mar 12, 2008
I have been running 5 reports to gather company information. I then take the data from these 5 reports and manually load them into an Excel spreadsheet to calculate a grand total. I would like to be able to run a query or report within my database to get the same information. I have attached a copy of my spreadsheet for review.
Could someone please review this spreadsheet and help me design an easier method of calculating a grand total.
View Replies
ADVERTISEMENT
Aug 5, 2014
I am writing a small program for my sons school for there athletics day. We want to register all the students on a data basis. I have a form where you capture all the child's information and then tick box for each event that there will be for the day. So we will only tick the events that the specific child will participate in. I design the table to just import the information from each school from a excel spread sheet directly into the table. What my goal is to print report for each event that will only show the child's names that has register for that specific event and all the different age groups separate. I am stuck at the query and can't seem to get my mind around how to define the query.
View 2 Replies
View Related
May 7, 2013
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
View 9 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
May 7, 2014
I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.
I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
[Code] ....
I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.
I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...
I have created a report from the above union query and it works perfectly with only one serial number entered.
View 14 Replies
View Related
Jun 2, 2006
Could you please help me out.
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct
2) If so, how do i do it as i have never done it before
3) If the above is not the best solution/ does not work... what will/is
View 4 Replies
View Related
Mar 27, 2008
Hi,
I need to create a report for each employee in my company of all compensation information. The report is going to need to have multiple detail sections on the report for example to list all benefits specific to the employee and all beneficiaries.
I tried using a subreport to produce those detail sections that I needed but once I placed this subreport inside a box on the main report each detail I created increased the size of the box. I also thought about making the detail section a list box and writing all of the information there. Would there be a better or easy way to accomplish this?
I am also thinking I am going to have to save all of this information in a table and based the report on that table?
Any help would be greatly appreciated.
Thanks,
tones
View 12 Replies
View Related
Jan 28, 2013
i want to create a summary report from multiple queries.
what are the best possible methods for this ? should i need to use subreport function or anything else?
View 1 Replies
View Related
Jun 12, 2013
I am trying to create a columnar report that relies on information that are expressions in various queries. Unfortunately, I can't seem to create them in one query because the criteria is mutually exclusive. I used the report wizard to start the report based on the single expression in the first query. Now I want to start adding the other data but I can't seem to get it to work. All I really want to do is include fields that have the have the value, which is already summed, from the other query. When I try that I get error messages like "you tried to execute a query that does not include the specified expression 'expressionName' as part of an aggregate function' and it just goes further downhill from there. What is the easiest way to get this simple values into the report? Is there a way I can create a field in the report that uses a SQL statement for just that field so I don't have to have the report linking to 50 different queries?
View 14 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
Oct 7, 2013
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
View 14 Replies
View Related
Jun 23, 2015
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
View 2 Replies
View Related
Jan 8, 2014
I'm currently working on a database which requires invoicing as a part of it. The invoicing is done based on quarters, and I want the users to be able to use a multiple items form, listing all of their clients, to create the invoices. Each invoice must be created individually so they can be e-mailed to the client, and saved to the clients folder. So I was wondering if it would be possible to create individual invoices for clients using a multiple items form.
View 1 Replies
View Related
Nov 26, 2014
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
View 5 Replies
View Related
Jul 19, 2012
I'm working on a contact database for my company's sales efforts. It tracks interactions with prospective customers and assigns them to employees here. My question is this: For mass contact efforts (such as mailings), is there a way to create mass entries to my "Interactions" table so that no one has to enter a mailout interaction 1000+ times?
Structure of the relevant bits of the database is as follows:
Contacts Table
Interaction Table
Contacts Table is linked with Interaction table via ContactID
Queries are run to narrow Contacts by various criteria (ex.: by the employee they're assigned to).
So if I run a query to get all the contacts assigned to me, and there are 450 of them, and I send a mailout to all of them, and I want to add an interaction to those 450 people, is there an automated way to do this?
View 5 Replies
View Related
Aug 31, 2007
Hi all, i hope this is posted in the right place first of all :) apologies if not..
I have about 8 individual databases which will be used simultaneously by users. I wish to be able to have full control over these databases from a 'master database' including being able to delete the data in there (possibly import fresh data but not 100% needed) and export data to be analysed in Excel etc
I figure this can be done with access but if i'm on the totally wrong track i would be appreciative if someone could give me a push in the right direction..
If any more infomation is needed then let me know.
Thanks :)
Mikey
View 8 Replies
View Related
Oct 19, 2004
Sorry about the simple question, but my brain is not functioning today. How do you create a report from a simple query?
View 2 Replies
View Related
Sep 6, 2004
I've been reading up quite intensively on using QueryDef's collection to try to pass a Parameter to a Query in order to then Open a Report. But can't seem to get it to work. I've been trying to write an SQL statement that the VB will accept in code but never got it to work. Depite copy/pasting the SQL from the Query Access creates. regardless.. I really need some help with this one. Thanks. Procedure follows. Get error that Report not found... The name is correct... I just want to pass the integer (index) from the ListBox into the Query then Open the Report... Thats all...
Private Sub previewReport_Click()
On Error GoTo Err_previewReport_Click
Dim intSelectMonth As Integer 'The Index of the Item Selected in Form List Box
Dim db As Database
Dim smReport As Report
Dim smQuery As QueryDef
Dim month As Parameter
Set db = CurrentDb
Set smQuery = QueryDef![GetReviewsMonthQuery]
smQuery.Parameters![rvMonth] = 9 'Using 9 as September just for Testing
Set smReport = Reports![Review Schedule by Month/Analyst]
DoCmd.OpenReport smReport, acPreview
Exit_previewReport_Click:
Exit Sub
Err_previewReport_Click:
MsgBox Err.Description
Resume Exit_previewReport_Click
End Sub
View 2 Replies
View Related
Jul 18, 2005
Hello,
I can run a query but when I try to display this information on a report I get the following message:
The specified field “tblMachineData.TagName’ could refer to more than one table listed in the FROM clause of your SQL Statement.
Here is my SQL code from the quey.
SELECT tblMachineData.TagName, [tblMachineFloat Query].DateAndTime, tblMachineRunTag.TagName, tblMachineFloat.Val
FROM tblMachineRunTag INNER JOIN (tblMachineData INNER JOIN ([tblMachineFloat Query] INNER JOIN tblMachineFloat ON [tblMachineFloat Query].DateAndTime = tblMachineFloat.DateAndTime) ON tblMachineData.TagIndex = [tblMachineFloat Query].TagIndex) ON tblMachineRunTag.TagIndex = tblMachineFloat.TagIndex
WHERE (((tblMachineFloat.TagIndex)=0 Or (tblMachineFloat.TagIndex)=1 Or (tblMachineFloat.TagIndex)=3 Or (tblMachineFloat.TagIndex)=4))
ORDER BY [tblMachineFloat Query].DateAndTime DESC;
Any help is appreciated,
Bob
View 2 Replies
View Related
Nov 9, 2014
I am quite new to MS access and started to develop a small database for my practice. I have made four tables that are for Patient details, their doctor detail, their problem and their treatment. I have successfully created form to enter the data.However I an unable to proceed, now I want to create a discharge letter which will be addressed to the doctor (based on the tables described above)
I want to make this in a way that if I enter the patient details i-e date of birth, ID and / or name a letter is created based on the tables above. I also want this letter to be saved for future use. how to design that letter and should it be a query or report.
View 6 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
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Jun 12, 2014
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name
Start Date
Midpoint Date
Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
View 14 Replies
View Related
Feb 19, 2014
I am using Access 2007. creating a report that show only one record from my query or table.
View 5 Replies
View Related
Dec 21, 2004
I have a database which contains around 60 queries that I need to turn into reports. The problem is, the wizard won't work because all of the queries contain the same fields. I would greatly appreciate feedback on making this process dynamic. Thanks in advance!
View 1 Replies
View Related
Feb 19, 2014
I'm having problems with an Update query. I have data coming in on one file that I am loading to a temp table that I want to update on another table in my database. The problem is that when I run my update query, it is wiping out everything that DOESN'T match. I have 5 fields that MUST be equal before the update can occur. I'm trying to update 3 fields in my "master" table.
Here is my SQL:
UPDATE NPPDcopy LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) SET NPPDcopy.Import = [tempNPPD].[Import], NPPDcopy.Export = [tempNPPD].[Export], NPPDcopy.Net = [tempNPPD].[Net];
View 2 Replies
View Related
