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.
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.
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.
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)?
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.
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
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?
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?
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...
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.
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.
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.
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?
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.
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
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;
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.
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?
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?
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]))
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!
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];
