Queries :: Show All Records If Any L Field Starts With D

Nov 20, 2014

I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.

SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table5
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));

View Replies


ADVERTISEMENT

Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View 5 Replies View Related

How To Show Several Records In One Field

May 21, 2012

I have a table: Complaint

Within the table there is a field: CustomerID

There could be anywhere from 1 to 10 records with the same CustomerID.

I would like to show all the complaints for a specific CustomerID on a form in one field.

Right now I have the following Control Source Code:

=[Complaint.PartNumber] & ", ," & [Complaint.PNdescrip] & ", ," & [Complaint.Complaint]

This code will only display 1 record.

I would like the text box to look like this: (All from the same CustomerID number)

Part Number A PN Description A Complaint A
Part Number B PN Description B Complaint B

View 2 Replies View Related

Queries :: Show Only Percentage Of Records

Nov 7, 2013

Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?

View 3 Replies View Related

Queries :: Duplicate Records Do Not Show

Feb 20, 2014

How can I have this query to show no duplicate records.

I would like to only see the first record only of each stock code.

View 2 Replies View Related

Queries :: Only Show Records That Do Not Appear In Another Query

Jul 30, 2013

Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.

View 3 Replies View Related

Queries :: Duplicate Records Show Only The Last One

Jun 24, 2015

I have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.

View 3 Replies View Related

Queries :: Show TOP N Records In Table

Jun 15, 2014

I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.

View 2 Replies View Related

Queries :: Show All Blank Records?

Nov 19, 2014

how to have our query show ONLY the records with NO DATA in the company field?

View 12 Replies View Related

Queries :: How To Get Multiple Records To Show As One

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

Show Records With Unique Field

Jun 8, 2006

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

View 1 Replies View Related

General :: Yes / No Field To Show All Records

Oct 25, 2012

I have txt input [txt choice] which has a number (1-9) and then a query which uses the following formula to define which records are visible linked to a Yes/No field

Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,Yes or No)

If the choice is "4" then it works fine, but if the choice is anything but 4 it still shows only the Yes items. If i change the code to

Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,No)

Then it does show just the 'No' items but i want it to show 'everything' if the choice is not 4

View 2 Replies View Related

Queries :: Joining Two Tables - Show All Records

May 31, 2013

I have a query joining two tables. TableInvoice in the query is Product number and Date purchased. TablePrices contains the same fields and contains prices (quarterly) over the past five years.

Joined by PONumber, I want the query to find the price charged for that date. Right now, if the Product number (TableInvoice) is not listed in TablePrices, it won't show. So, 100 Records might return only 80 if TablePrices does not have all of the Product numbers.

Is there a better way to return all 100 records in TableInvoice and show (Blank) price data if the item is not in TablePrices?

View 1 Replies View Related

Queries :: Only Show Records Where Any One Of 6 Fields Is Not Null

Mar 17, 2014

I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.

I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?

Code:

SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;

View 2 Replies View Related

Queries :: Sorting - Show Records From This Week Only

Apr 23, 2013

I'm doing a query and I want it to show records from this week only.

Is there a formula I can use in criteria?

View 3 Replies View Related

Queries :: How To Show Records Based On Date

Nov 8, 2013

I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information

On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly

Then, on another form someone else can check what 'Jobs' should be done on that day

I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)

The problem is that i have been limited to the options of 'repeatability'

What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"

Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?

I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.

View 7 Replies View Related

Forms :: Show Records With One Common Field Value

May 23, 2013

I'm a beginner trying to build a simple database for work. I'm trying to track the performance of bonds from month to month, in this case January-April. Each bond has a serial number called Serial_ID, but we wanted to create a record for each month we own the bond, so we created a primary key combining Serial_ID and start date called Serial_Start_Date. So if we own one bond, its January record would have a primary key of "4553123A 2013-01-01" and February would be "4553123A 2013-02-01". I created a form that allows a user to select one individual month's bond and display that month's return. However, I'm struggling to find a way to create a form that allows me to just select the Serial_ID and display the four month's records. Right now there is only one table in the database.

View 1 Replies View Related

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

How to get the following results using 1 table:

Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it

Table A

Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8

Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8

I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.

View 5 Replies View Related

Queries :: Query To Show Only Records With All Parts Received

Apr 10, 2015

I would like to create a query to show only the Work Orders that all parts ordered have been received.For the Work Order, each part ordered has a "complete" yes/no checkbox.If I use that field in the criteria of the query, it does return only parts that are "complete." However, it still shows me a Work Order where there are parts ordered but not received "not complete." how to set the query to exclude those cases and only return Work Orders that all parts ordered on the Work Order are received "complete."

View 3 Replies View Related

Forms :: Field To Show Count Of Records Between Dates

Apr 30, 2014

I'm creating a form to count the number of employees with birthdays between 2 dates. There are 2 unbound date fields; Start_Date and End_Date. I have an Employee table with DOB field. I've been stuck on how to get the field to return the correct number of employees that fall within the 2 dates.

View 2 Replies View Related

Queries :: Get Rid Of Duplicate Records That Show Different Data In Multiple Columns

Sep 8, 2013

I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training 1/1/2010 5/1/2010
1 John Smith Python Training 1/30/2011 4/1/2011
1 John Smith Leadership Development 6/27/2013 9/1/2013
1 John Smith Sensitivity Training 9/5/2010 -
2 Hank Joel MS Office Training 8/1/2010 10/1/2010
2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010
2 Hank Joel WHMIS Training 11/15/2010 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

To turn like this:

Code:
Employee # Name Course Start Date Completion Date
1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, -
2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010
3 Jane Doe Leadership Training 7/18/2011 9/26/2012

I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:

Quote:

SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];

However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:

Quote:

WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")

I placed the following in VBA module:

Code:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _

[code]....

View 4 Replies View Related

Need Report To Show A Number Of Records Based On A Value In A Field Of A Table

Aug 9, 2006

Hi,

I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description

Data example:
1001, 5, Jackets
1002, 10, shoes
etc

I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets

and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes

If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.

Can anyone help?

View 1 Replies View Related

Queries :: Left Join Query To Show Records That Fulfill Two Criteria

Mar 25, 2014

I have two queries. The unique key in both queries is GUID for katalogposition.

One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.

The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.

The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.

1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".

I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.

How can I do that? Is there another way to do it? Please see attachment.

View 3 Replies View Related

Queries :: Filter Using Date Range But Show All Records If Text Box Null

Jan 13, 2014

I m trying to make form which filters my records and generates a report..here's where I am

Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"

but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code

Code:
Like "*" & [Forms]![Form1]![Text6] & "*"

the code is in report record source

View 9 Replies View Related

Queries :: Show Most Often Used Data In Field

Jun 29, 2014

How can i make a query which shows the most used data in a field, i saw how i can show the biggest or smallest but i didn't see how many times is each data used in a field.

My field is for years, and i wanna see each year and how many times it is used.

Is this possible to do?

View 4 Replies View Related

Queries :: Show All Field In Query

Jun 3, 2015

I have a database about certain accounting datas from week by week and it's growing. I should make a Crosstab query for see the amounts weekly, it's will be exported to an excel workbook, wich have macro's(this is the problem, because the exported field will be bigger week by week). The difficulty of this query is the future weeks. I want to see all of the weeks in the columns. I made a table wich contains the weeks(Hetek_1.CW) wich I want to see, and the source is also contains the items accounting weeks.

Code:
TRANSFORM Sum(CWall_hetekkel.[Knyv# ssz# kltsg]) AS [SumOfKnyv# ssz# kltsg]
SELECT CWall_hetekkel.[Ktgh# kdja], Oka.Oka
FROM Oka RIGHT JOIN CWall_hetekkel ON Oka.Oka = CWall_hetekkel.Oka
WHERE (((CWall_hetekkel.[Ktgh# kdja])=1250 Or (CWall_hetekkel.[Ktgh# kdja])=1251 Or (CWall_hetekkel.[Ktgh# kdja])=1252 Or (CWall_hetekkel.[Ktgh# kdja])=1253))
GROUP BY CWall_hetekkel.[Ktgh# kdja], Oka.Oka
PIVOT CWall_hetekkel.CW;

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved