Queries :: Multiple Records Appear In Output Of Query
Mar 4, 2015
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
View Replies
ADVERTISEMENT
Mar 13, 2007
Hi
I am writing mini database which will produce labels for boxes. I want to be able to produce a sheet of labels based on the 'number of labels' to print field. I can output one label or many on the page/s depending on how I structure the query and how many records are in the query.
My Question:
I need is a query which takes an individual record and duplicates it by the integer in the 'Number of Labels' field
any ideas?
thanks
View 3 Replies
View Related
Dec 15, 2004
Hi,
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments
???
View 2 Replies
View Related
Mar 25, 2013
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** *************
SELECT TABLE1.ID,
IIf([TABLE1]![STAGE]="D",
IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1",
IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
[Code] .....
View 1 Replies
View Related
May 21, 2007
I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.
The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '
Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.
I need some sort of statement to say if there are no matching records please display a zero.
Any help would be much appreciated!
View 1 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
Dec 12, 2013
I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.
I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.
I'm not sure how to set up the new field.
View 10 Replies
View Related
Feb 13, 2014
I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
View 5 Replies
View Related
Jul 1, 2015
How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:
Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)
Output of a query is a single MAX value.
View 4 Replies
View Related
Jul 1, 2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
View 5 Replies
View Related
Aug 14, 2013
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
View 2 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
Apr 18, 2014
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
View 1 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Jul 11, 2013
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
View 7 Replies
View Related
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
Jul 31, 2013
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 ....
View 2 Replies
View Related
Feb 6, 2015
I have several reports that creates price sheets for all of my customers. It is a report with multiple subreports within, and I am trying to create a process to seperate them and export them all into [Customer Name].pdf. I have tried to scrap some code together from various forums, and it has been unsuccessful.
For example, one of the reports "rptPriceSheetQuarterly", has a field "txtCustomerName", that feeds the queries for the other subreports. I would like to create a new PDF when that field changes.
I understand I need some sort of looping code that will cut the report up dump them all into one folder, but I am totally out of my element here.
View 14 Replies
View Related
Jun 15, 2005
Hey everyone,
Even though I'm going to feel like a moron for not knowing this I'm hoping there is a simple answer. I'm far from a master at access, and seem to be having a little trouble when it come to formatting the output of my query. I have a database made the keeps track of about 200 hundred crime reports. I want to be able to do a query that will output the 15 possible crimes as rows, and then have 4 columns which are the locations of where the crimes happened. And then for the results have how many of crime where commited there. Pretty much in an excel format (see below example). However there are some crimes that have never happened. So there are no records of them. Unfortunatly when i do my query since there are no records, they are not even listed. I need them to list the crime, and place zeros if there are none. Since this probably isn't to clear I'll put a little example below.
Data:
Robbery: 4 on campus, 5 off campus, 6 in city, 0 in apartments
Rape: 0 on cmpus, 3 off campus, 4 in city, 0 in apartments
murder: 0 on campus 0 off campus, o in city, 0 in apartments
When i do my query it looks like this
*********On Campus****Off Campus****In City
Robbery******4***********5***********6
Rape********************3 ***********4
*Notice it doesn't even list the apartments since there are no records that match, the same with murder.
I need the output to look like this:
*****On Campus *****Off Campus ***In City****apartments
Robbery****4 ************ 5********6 ********0
Rape*******0*************3********4********0
murder *****0*************0 *******0********0
Any ideas? Thanks!
View 2 Replies
View Related
Feb 12, 2006
Hi,
I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
Thanks!
Andre
View 1 Replies
View Related
Jan 14, 2007
Hello again
Sorry, just posted this in reports, moved it now.
Could someone point me in the right direction please. I have seen a thread similar to this before but I can't find it now.
I have a query that is made up from 3 different queries.
These 3 queries show the stats totals in the last x weeks.
qry2Week (colleague stats from last 2 Weeks)
qry6Week (colleague stats from last 6 Weeks)
qry13Week (colleague stats from last 13 Weeks)
This query shows every day and also the above 3 queries are linked in.
qryAll (every day and 2, 6, 13week stats totals)
The problem is if there are no stats in the last 2 weeks no stats will show up at all.
If I take out the (is not null) from the individual queries I get duplicate lines in my main query (qryAll)
How do I show all the information without having duplicate lines.
Cheers
Kev.
View 3 Replies
View Related
Feb 17, 2015
I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCal Equipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:
Code:
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());
how to get the query for the report to return the records.
View 10 Replies
View Related
Jun 13, 2014
I have a database that contains different departments per office location with the dept. codes such as 100, 101, 102, 103 etc.
I like to have a criteria in a query that will give me all departments that are running from one office location OR if I do not want one dept. to show in my query to be excluded.
Also, the way I currently set the parameters is, it is asking for the office location by state, county, city, address and department code. I set the department criteria as Not [Department] which excludes the dept. that I do not want to see in the query, BUT I also want to have an option that when I run the query to SEE all the departments.
Is it possible to have a criteria like that?
View 3 Replies
View Related
May 4, 2013
I have a query which is supposed to show the revenue from each advertising source.
PLease see attached in zip file (cant post pics b/c I have less than 10 posts)
As you can see it has revenue amounts for the same source of advertising:
Radio has two amounts, INternet has two etc..
What can I do to only have each form of advertising show up once?
and the revenue amounts summed up for each advertising medium.
View 2 Replies
View Related
Apr 28, 2005
Hi every one. i want out put records into a list box with 3 column headings named as playerno, initials and name. The way i created the list box is by putting list value for the row source type and name the list box as lstPlayers. But unfortunately get all the the records Fields one after one not one row at a time. I be happy if some one tell how to fix this .Thanks
Private Sub cmdSelect_Click()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cn.Open ("Provider=MicroSoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:db.mdb")
rst.Open "select playerno, initials, name from players order by name, initials", cn
rst.MoveFirst
Do Until rst.EOF
lstPlayers.RowSource = lstPlayers.RowSource & ";" & _
rst("playerno") & ";" & rst("initials") & ";" & rst("name")
rst.MoveNext
Loop
'TextBox = lstPlayers
'MsgBox (playerno)
rst.Close
cn.Close
End Sub
View 3 Replies
View Related
Aug 18, 2005
when running the attached report I am getting duplicate records. If using the protocol specified tc02026 there should be 8 records total. When running the report it pulls from both queries which have select criteria. How do I limit the report to only give me the 8 records and eliminate the duplicates? When I run each query it only gives me the 8 records I am looking for. Each Sample# in the report is a unique number(to help see the replicates).
Thanks
View 1 Replies
View Related
