Reports :: Query Which Returns Values From A Table
Jul 27, 2014
I have a query which returns values from a table, as follows:
Service_Date
Supervisor
EExpect
EAchieve
OExpect
OAchieve
IntLossOP
IntLossEN
This is used for a report for a 28-day period (the query selecting the Start Date and the End Date), and the intention is to show the information grouped by Supervisor, showing the information for as many days as they worked.However, I would like to have the overall 28-day averages of the six fields EExpect thru to IntLossEN shown underneath the detail for each individual. I know I could do this in Access Totals in the page footer if the report was confined to one supervisor, but with up to 20 supervisors I am unsure how to get these totals after the detail for each?
I would also like to have this summary information for each individual presented on a separate page at the end of the report (almost like a league table, if you like), so there is a single page that shows the data for each supervisor in comparison to all their colleagues.
View Replies
ADVERTISEMENT
Oct 29, 2007
I'm trying to execute a query which is looking at data from 2 different tables and most of the data fields in the tables are the same. For instance table 1 has Dept, Res, Period_date, & Available Hrs and table 2 has Dept, Res, Period_date, Project & Forecast Hrs. The results I'm looking for are as follows:
Dept Res Period_date Project Forecast Hrs Available Hrs
11 A 11-02-07 123 XXXXX
11 A 11-02-07 456 XXXXX
11 A 11-02-07 789 XXXXX
11 A 11-02-07 012 XXXXX
11 A 11-02-07 XXXXX
But the "Available Hours" are duplicated in the preceeding 4 rows.
Thanks for any advice on this.
View 5 Replies
View Related
Aug 30, 2014
My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.
View 4 Replies
View Related
Dec 10, 2007
Hi
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
My knowledge of Access is Intermediate.
Kind regards
View 14 Replies
View Related
Apr 24, 2006
Check the attached mdb file.
Can't figure out why the query returns -1 instead of the letters that are stored in the table :confused:
Many thanks!
Steff
View 2 Replies
View Related
Jan 31, 2008
This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.
Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.
Here is my sample sql:
SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable
FROM tblAddressBook;
The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.
Thank you.
View 4 Replies
View Related
Jun 28, 2013
I am making a report off of a query. The report is returning values from a Option Group. 1=Yes, 2=No and 3=N/A. The report returns the numeric values and I want the value labels instead. So, I created a column in the query for exp1 which looks like the following:
=IIF([VSArrive]=1,"Yes",IIF([VSArrive]=2,"No","N/A"))
What the report returns is #Error.
View 6 Replies
View Related
Mar 2, 2007
Advanced apologies for the long question.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
Can anyone figure out what I'm doing wrong?
Many thanks in advance
View 3 Replies
View Related
Oct 22, 2013
On a form I have the following field:
Name: PaymentsMade
Control Source: =DLookUp("SumOfPrePaymentValue","qryInvoicePrepaym entSum","InvoiceId = " & [InvoiceId])
In the form's footer I have the unbound text control:
Name: SumOfPaymentsMade
Control Source: =Sum([PaymentsMade])
SumOfPaymentsMade returns #Error
How do I get it to return the sum of PaymentsMade?
View 4 Replies
View Related
Aug 2, 2013
The following code is in a text box on a report in a database that creates work contracts. The IIF statements and Dlookups pull the parts of the text together perfectly, but why the new line and carriage returns are completely ignored in the last section? Been working on this all day and we can't find a way of inserting 2 new lines!
Code:
=DLookUp("Description","QFRCL07") & (IIf(DLookUp("Description2","QFRCL07") Is Not Null,DLookUp("BasicSalary","Tmain","ContractID=ContractID") & " " & DLookUp("Description2","QFRCL07"),"")) & (IIf(DLookUp("Description3","QFRCL07") Is Not Null,DLookUp("Job","Tmain","ContractID=ContractID") & " " & DLookUp("Description3","QFRCL07"),"")) & (IIf([Forms]![FFRRCheck]![Text305] Is Not Null,(Chr(13) & Chr(10) & Chr(13) & Chr(10) & [Forms]![FFRRCheck]![Text305]),""))
View 5 Replies
View Related
Jul 5, 2013
If one of the fields are 0 then it returns this error on my Reports in the Field and/or in the Sum of these. I read somwhere to create a tblDivByZero table but since I'm a novice I still can't make this work.
How to Elimate #Div/0! Errors in MS Access Reports
View 9 Replies
View Related
Apr 18, 2013
PDF was generating and saving to the correct location, but when I open the PDF file ... it's blank =P am using access 2003 and acrobat standard 9
Code:
Private Sub cmdPDFByAssetNo_Click()
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
[code]...
View 2 Replies
View Related
Nov 20, 2013
Objective to have a report generated from a query (Based on a Storage Table) in the record-source however need to make adjustments to each record based on the query output ie User Id is outputted as number as it needs to be for everything else but on the report loading needs to run an sql query on that ID number to get the name and then replace that value with the name and so on for each record and each appropriate column
Problem:I can either change All of the records outputted with the first records info or nothing at all. i need this process to occur individually on each outputted record
View 10 Replies
View Related
Aug 6, 2014
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies
View Related
Jun 16, 2015
I am trying to create a pivot table to show total absences. Each absence is an individual occurrence with a with a count of 1 in the attendance column. All I need for the table is the ID and the Attendance; ID is my row and attendance is what I need to be summarized but I just keep getting a zero in each place where the Attendance is a 1. This works well in Excel. What is different here?
View 2 Replies
View Related
May 11, 2013
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
View 1 Replies
View Related
Feb 25, 2008
The following query never returns, even though there is an index on the name field in the Patient table:
SELECT p.Name FROM sheet4 AS s INNER JOIN Patient AS p ON p.NAME LIKE s.LASTNAME + ', ' + s.FIRSTNAME + ' %'
View 4 Replies
View Related
Aug 30, 2005
Obvious neophyte issue here, sorry...
I have a table, PURCHASE_ITEMS with 3 fields: ID, TYPE, NAME
Another table, ITEM_TYPES with 2 fields: ID, TYPE
TYPE in both tables is a text field and there is a one-to-many relationship between them.
When I run a query on PURCHASE_ITEMS, I can see all 25 records. When I set a criteria for TYPE to one of the types, no records appear, even though there are 5 or 6 of that type.
Can anyone help me with this absurdly simple problem that I can't seem to get my brain around?
View 1 Replies
View Related
Nov 26, 2006
I am creating a database for a hyperthetical car hire company. A customer hires a car from and until a certain date. If a new customer decides to hire a car i want to generate a list of cars that he can choose on depending on the other dates from which other cars are hire from and until. I have created a query that generates all of the cars that the new customer can not use.
I have also generated a list of all of the cars, in the database.
I have created a new query takes the numberplates of all the cars and subtracts the numberplates of the cars that are being used.
This is where i hit a problem. For some reason the query is generating the list of number plates twice and then subtracting the numberplates that are being used. This leaves me with with 2 values for every numberplate that can be used and 1value for every numberplate that cannot be used. Can you help me?
Please post your email address so i can send you the zip file as the file is too large to upload. The query that i am having problems with has the title:SEARCH FOR AVAILIABLE CARS.
Thank You
View 2 Replies
View Related
Feb 7, 2007
Hi all,
I think this could be another one of my stupid moments. Here is my query expression -
IIf([WHAT TEAM?]=1,[strCurrent_Team]="FINANCE",[strCurrent_Team]<>"FINANCE")
When run, the user is prompted to enter a team number. If they enter 1, then by my reckoning the query should return all records where [strCurrent_Team] = "Finance".
However, no records are returned.
strCurrent_Team is formatted as text.
Can anyone tell me what I am doing wrong here?
Cheers,
Rob
View 3 Replies
View Related
Jul 29, 2007
I hope this doesn't sound too simple for this forum, I'm only a newbie!
I have a select query that will display the recordset that meets the criteria (OK I know that's not a big deal) but...I want to know if, when there are no records that meet the criteria, can I open a form (dialog box maybe) that will say there are no records found instead of showing a blank recordset. Once again I hope this is not so blindingly obvious that I have to start watching my coffee intake
Thanks in advance
View 2 Replies
View Related
Mar 5, 2008
Hi, i'm hoping someone here might be able to help me. I have come to a bit of a dead end with a database application i am working on.
The database is a delivery newspaper management system for a newsagents. Basically i need it to link customers to the paper they wish to receive, organise them into delivery rounds, and produce a bill for each customer based on the newspaper they get delivered.
The problem I am currently having is when it comes to the billing.
I have:
a table that stores customer details
a table that stores newspaper details (inc. price)
a table that stores the customerID and then the NewspaperID for each day of the week. (since not all customers get a paper everyday of the week)
If a customer gets two papers then two entries are made in the requirements table under there CustomerID)
Now to fetch the price of the newspaper the customer is down to receive each day and then add these all together is where i have been having trouble.
After many different attempts the solution i am currently using is this. I have a query for each day of the week. Each query take the customerID from the requirements table, then the newspaper price from the table storing newspaper details.
The problem here is that the query returns the CustomerID next to every newspaper in the database. I found the solution was this. I added the newspaperId from the newspaper details table and in the criteria stated "[tableRequirements].[NewspaperID]" And this worked perfectly.
Untill i added a customer who gets two papers. As far as i can tell the query is returning all the possible combinations of which newspaper the customer should get.
Any help with this would be greatly appreciated - thanks in advance.
View 1 Replies
View Related
Nov 3, 2011
I am trying to find an Access solution to handling the MAX 2/3rds (I.E. 2from3 or 8from12 etc) returns from a series of "Scores" of a league table which will have many players, playing in many matches. I have created a crosstab query which delivers the total scores for each player from each game and played around with a make table query and a report output which I have used in excel to deliver the best 2/3rds of the scores. As others will be using the completed data base I would like to keep the solution within Access but I cannot work out how or if this is possible. Below is a limited output from the crosstab query showing the Total of all games played which I wish to turn into best 2/3rds.
View 5 Replies
View Related
Apr 9, 2007
Hi,
I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.
I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.
Any help you can give me to return just the one result needed would be greatly appreciated.
Mompo
View 8 Replies
View Related
Nov 9, 2007
This is at least the 3rd time I've come across something that is, to me, an extremely serious bug in Access. Anyone else seen this, and anything I can do to avoid it??
In a query, I ask for the values from Field A.
The query returns the values from Field B, but still calls it Field A.
This is in a fairly complex query. If I delete one particular field from the query, the bug disappears. If I put that field back, the bug returns.
Here is my current query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.FirstOfFeederSize, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST, [loadssavedperday]*[lostpersetup]*[std_cost]*250 AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));
HEre's the pertinent part of the record from BYPN_1:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4
But here's what the query returns:
PartNumberLostPerSetupFirstOfFeederSize
107573 12X4 12X4
Now, if I just delete the "parts_annual" field from the query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.FirstOfFeederSize, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));
Now the query returns what I expect:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4
View 14 Replies
View Related
Jun 11, 2014
I'm trying to assign the result of an SQL query to a variable using VBA in Access. The query returns a value but the variable which it is assigned to has a value of Nothing. Here is the code snippet:
Dim queryReturnID As String
queryReturnID = "select dbo_tbl_SupplierReturn.ReturnID from dbo_tbl_SupplierReturn" & _
" where SupplierID = " & lstPOHdr.Column(1)
Debug.Print queryReturnID
Dim RecordSet1 As DAO.RecordSet
Set RecordSet1 = CurrentDb.OpenRecordset(queryReturnID)
View 3 Replies
View Related
