Queries :: Max Not Showing Only Biggest Date

May 13, 2015

I have 3 Tables with relationships between Pupils to Read and Books to Read:

Pupils ID, Forename, Surname
Books - BookID, Book, Author, Level, Genre
Read - ID, BookID, DateOut

On another thread in the forum I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books.

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book
FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

View Replies


ADVERTISEMENT

Queries :: How To Get A List Of ALL Equipment Showing Most Recent Date And Time

Mar 23, 2013

I've got three tables:

Code:
tblequipment
equipmentid
equipmentnumber (user defined ID)

tblrentals
rentalid
rentaldate
rentaltime
fromparty (c for customer, e for employee, o for other)
frompartyid (foriegn key to either customer, employee or other)
toparty (same as from)
topartyid (same as from)

tblrentaldetails
detailid
rentalid
equipmentid

How can I get a list of ALL equipment showing the most recent date and time, also showing the respective toparty and topartyid? I can get it fairly easily, except for including toparty and topartyid.

View 2 Replies View Related

Picking Biggest Default

Mar 21, 2006

I have a table with ID1, ID2, and AMT.

IF ID2 is NULL, I want to update it with the non-null ID2 where the ID1 matches and has the largest sum of AMT.

For instance

ID1 ID2 SUM(AMT)
1 333 1000
1 444 3000
1 <null> 5000


In this case, the third ID2 would get the value of 444 since it had the highest amt.

I'm guessing this involves sub-queries, and I have a real blind spot where they are concerned.

Thanks,
David

View 3 Replies View Related

Finding Field With Biggest Number Of Characters

Feb 26, 2008

I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.

Is there a way to find this with a formula or expression?

View 3 Replies View Related

Reports :: Showing Date Field Differently Depending On Current Date

Nov 23, 2013

I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.

So:

- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY.
- If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY
- If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY

I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY

View 9 Replies View Related

Display Fields As Column Showing Date One Month After The Date In Field Above

Jun 2, 2015

I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.

The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.

I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!

Now I could do that in Excel, but I don't know how to get Access to do it.

View 4 Replies View Related

Queries :: Quotations Database - Queries Showing Too Many Records

Jun 25, 2015

I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.

View 1 Replies View Related

Date Showing As Invalid On Table

Oct 6, 2006

hi.

i was wondering if anyone can help me with my problem.
im using a linked table to populate a report. the table is linked to an SQR output in a text file. one of the fields in the text file is a date in the format 31-Dec-2006.

when i create the link and set the format of the date field to date/time, it shows up as #Num! when i open up the table in Access and it does not show up at all in the report. can anyone explain why this is happening?

on the other hand, when i format the field as text in the table, it will show up both on the table and the report properly. will i be able to make a report filter if the format of the date field is text?

thanks

View 1 Replies View Related

Reports :: Leading 0 Not Showing For Date On Report

May 28, 2015

On report, date shows as: 5/23/2015

I want it to show as: 05/23/2015

The input date in main table has the same format as the fields without the '0'.

Property for all date fields appear the same...

View 5 Replies View Related

Warning Showing A Date Will Expire In 30 Days?

Apr 30, 2013

I am trying to create some sort of warning to show that a date in one of my forms will expire in say 30 days. I need the warning to be a pop up (or similar) rather than be sent via email (only because I don't have Outlook set up and probably will not be able to). Effectively, I have a list of names and a column in my table to show an expiry date. To avoid missing the expiry date, I would like to be advised of this at least 30 days before hand.

View 3 Replies View Related

Reports :: Showing Image Based On Another Field (Date)?

Apr 8, 2013

I need to show an image called PAID if my hidden field Paid Date has a date in.

I thought this would be straight forward but so far nothing.

I have my images within the database itself on Access 2010, so I am not referencing any in a C drive or other location.

Code:
Private Sub Report_Current()
On Error GoTo ErrHandler
If paiddate = Date Then
Paid.Visible = True
Else
Paid.Visible = False
End If

ErrHandler:
MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

But with no joy.

View 8 Replies View Related

Tables :: ODBC Linked Table Showing Only MAX Date

Jan 22, 2014

Using access 2007 (and tried 2003) I have a linked ODBC table which is using an ORACLE driver. If I query the table directly through ORACLE/SQL for a particular 'job number' I get the following results:

18598979 3 06-DEC-13 10:10
18598979 0 03-DEC-13 10:34
18598979 1 03-DEC-13 10:34
18598979 2 06-DEC-13 08:20

However when I link the table through access and view the same job number within the table (not even using a query) it displays the following:

18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00

As you can see it only displays the MAX date/time and associated data from the 4 available records, duplicating the record. I've checked the field formats and they all look fine. It does the same if I pull through the table on it's own or with other linked tables.

View 2 Replies View Related

Queries :: Showing 0 If No Results?

Mar 5, 2015

I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?

View 9 Replies View Related

Queries :: IIF Statement Showing All

Nov 10, 2013

I need an iif statement in my query criteria that works as follows:

Code:
iif([Field] = "FirstPossible","str1",iif [Field] = "SecondPossible","str2",*)

The problem is, I'm not sure how to display all of the records if neither the first iif or second iif returns true.

View 8 Replies View Related

Queries :: Query Showing Two Results?

Mar 19, 2013

I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.

The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?

View 4 Replies View Related

Queries :: Showing Available Vehicles By Dates

Mar 26, 2013

We have vehicles which are lent out to employees on a daily basis,

I currently have a query shown as a report which vehicles are out between the given dates using this criteria in a query

>=[ENTER Start Date] And <=[Enter End Date]

what I want to be able to do is show which vehicles are NOT being used between these dates tried a few different ways but no success

View 5 Replies View Related

Queries :: Showing Running Total For Each Month?

Oct 12, 2013

I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.

The incoming revenue is a field of running sum of revenue for each product.

Desired output:

I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.

I am attaching the database, with sample data.

View 1 Replies View Related

Queries :: Dates Showing Up For Multiple Months?

Jun 18, 2015

I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.

I started running this query in may, that's why it only shows may and june's data.

I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know

View 14 Replies View Related

Queries :: Search Form Not Showing All Results

Jun 18, 2015

I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with

Description
CarNum
SerialNum
Category
Condition

In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)

The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.

View 5 Replies View Related

Queries :: Combining Query Not Showing All Results?

Jan 29, 2015

Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).

A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).

I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...

My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:

1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)

View 7 Replies View Related

Queries :: Query Not Showing Data From Table

Dec 12, 2013

I am trying to run a query and for some reason its not showing the data from the table (its blank), this is a monthly reporting I do - last months query works perfectly.

Table: log and list

Here is the SQL

SELECT log.*, UL.langue, UL.version
FROM log, (SELECT list.id, list.[langue], list.version, list.no_joueur FROM list GROUP BY list.id, list.[langue], list.version, list.no_joueur) AS UL
WHERE (((log.id)=[UL].[id]) AND ((UL.no_joueur)<90000000));

Table has all the columns.

View 1 Replies View Related

Queries :: Data Not Showing Up In Query But In Table

Jun 19, 2013

I am working with a database and existing query from my predecessor. The field in the query appears in both the database table and the query.when I run the query it doesn't appear. Is there a limit to the number of columns in a query I easily added a new column and moved a column,

View 9 Replies View Related

Union Queries - Results Showing As Text & Not Numbers

Dec 13, 2007

To cut a long story short, i've got a union query that is a full outer join of 2 tables.

Anyway, the results i have got back needed formatting to no decimal places.

How do i do that?

I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.

It's like it is showing as a text and not number.

Is there a way of converting the result to a number, particularly in a sql query.

View 2 Replies View Related

Removed And Returned Items Keep Showing Up In Queries & Reports

Feb 14, 2008

Good afternoon!
I have a database that tracks the withdraws and returns of shared tools.
The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.

When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.

I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.

Example...
I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.

The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.

The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.

Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it.
. One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.

This is the SQL:

SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
HAVING (((tblItemSpecifics.Removed)=True));

View 5 Replies View Related

Queries :: Using Query As DAO Recordset Showing Incorrect Results

Apr 26, 2013

I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:

Code:

Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value

[code]....

The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.

I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.

View 3 Replies View Related

Queries :: Select Distinct Field And Showing Other Fields?

Jun 4, 2013

How to query Select Distinct field + showing other fields? E.g.:

Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable

[Code] ......

View 7 Replies View Related







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