Hi Everyone,
I have a table which has many fields in it but I need to pull some specific
info via a query and I don't know if it's possible.
I want to run a query which includes two tables. Participants and Payments.
They are linked so that a participant can make many payments and a report
can then be printed on all payments that participant has made.
What I want to do is have a field in the payments table that has a "PaidTo"
field which is a date. If it goes past the date, I want to be able to run a
query that shows participants that are overdue. I have query that does this.
That was no issue. Where the problem ocures is that as there may be many
payments by the one participants, unless I remove the past payment enquiry
date, is shows the old payments as well yet I only want to show the last
one.
What I have is;
<=Date()-7 And Is Not Null
for the criteria of the PaidTo field. When I run the query, it shows a list
that may have the participant 10 times as they've made 10 payments. I want
it to only show the Last Payment "PaidTo" Date.
I hope that all makes sense! If anyone can help me i'd appreciate it.
Regards, Scott :)
2  3106 
Hint: It helps to post your query. Without that, we can only
guess....
On Thu, 8 Nov 2007 11:32:52 +0900, "scott"
<sc**********@m aneyacts.comwro te:
>Hi Everyone,
I have a table which has many fields in it but I need to pull some specific
info via a query and I don't know if it's possible.
I want to run a query which includes two tables. Participants and Payments.
They are linked so that a participant can make many payments and a report
can then be printed on all payments that participant has made.
What I want to do is have a field in the payments table that has a "PaidTo"
field which is a date. If it goes past the date, I want to be able to run a
query that shows participants that are overdue. I have query that does this.
That was no issue. Where the problem ocures is that as there may be many
payments by the one participants, unless I remove the past payment enquiry
date, is shows the old payments as well yet I only want to show the last
one.
What I have is;
<=Date()-7 And Is Not Null
for the criteria of the PaidTo field. When I run the query, it shows a list
that may have the participant 10 times as they've made 10 payments. I want
it to only show the Last Payment "PaidTo" Date.
I hope that all makes sense! If anyone can help me i'd appreciate it.
Regards, Scott :)
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
On Nov 7, 9:32 pm, "scott" <scott.nos...@m aneyacts.comwro te:
Hi Everyone,
I have a table which has many fields in it but I need to pull some specific
info via a query and I don't know if it's possible.
I want to run a query which includes two tables. Participants and Payments.
They are linked so that a participant can make many payments and a report
can then be printed on all payments that participant has made.
What I want to do is have a field in the payments table that has a "PaidTo"
field which is a date. If it goes past the date, I want to be able to run a
query that shows participants that are overdue. I have query that does this.
That was no issue. Where the problem ocures is that as there may be many
payments by the one participants, unless I remove the past payment enquiry
date, is shows the old payments as well yet I only want to show the last
one.
What I have is;
<=Date()-7 And Is Not Null
for the criteria of the PaidTo field. When I run the query, it shows a list
that may have the participant 10 times as they've made 10 payments. I want
it to only show the Last Payment "PaidTo" Date.
I hope that all makes sense! If anyone can help me i'd appreciate it.
Regards, Scott :)
It doesn't make sense to store "PaidTo" (a volatile value) in Payments
(a transaction table). You would have to update this field every time
a payment is posted. What you can do is create a new query showing the
max PaidTo grouped by Participant. Use the latter query to drive the
overdue report.You can join this to the Participants table to get
details for the participants.
Hope this helps! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: AndyBell |
last post by:
Hi all!
I have an Access 2000 database for the Habiat for Humanity where I work.
This is the second database I have written and it gets a bit more complex
each time... I have learned much and read much over the last few months and
have found leads to the answers to several other problems I have encountered
with a Google search of past posts/threads.
SO thank you for the help already rendered even though you didn't know!!
My problem...
|
by: Melissa |
last post by:
I have this table:
TblProjectYear
ProjectYearID
ProjectYearStartDate
ProjectYearEndDate
The Project Year will always span across December 31; for example 9/1/04 to
6/30/05. How do I build a combobox based on this table that will display all
the months between the StartDate and the EndDate for a given ProjectYearID
and when a selection is made, the full date of the last day of the selected
|
by: samotek |
last post by:
Filter the last date only
I am trying t0 filter only the last date of the query,but i cannot
manage that.
My query is the following :
SELECT Sum(.liters) AS SumOfLiters, orders.invoicedate
FROM orders LEFT JOIN ON orders.orderid = .OrderID
GROUP BY orders.invoicedate;
|
by: solar |
last post by:
I have a problem with building a totals query showing data
for the last date only.Under the last date i understand the last date
of field invoicedate.This invoice date is in the table orders.
Therefore in the column of the query i have entered :
DMax("InvoiceDate";"Orders")
I have also tried with :
DayNumber: DatePart('d';)
|
by: ice |
last post by:
Hello,
I have a couple of tables. The client tables and the contacted
tables.
I am not sure how to start on this, what I need is a way to query all
my clients
then show any client that the last visit and or called day is greater
than 30 days.
Now it gets confusing, Suppose the client was visited more than 30 days
ago
but was called only 10 days ago, I really would like to have this
| | |
by: schapopa |
last post by:
Hi,
I want to create query where I could group records by quarters, and get
the last record in each group.
e.g
Create Table MyTable
(
Value ,
date
)
|
by: dauwe.peter |
last post by:
A table : Nameperson, Book nr, Bookdatein, Bookdateout, CD nr,
cddatein, cddateout, dvd nr, dvddatein, dvddateout.
I would like a query where a see the personsname en de last book with
the datein and out , the last cd with datein and out , and the same
for the dvd. Because the last time the person came he toke only a
book but dit not return his dvd (for example). But with one click i
would like to see the last of every item with the...
|
by: paii, Ron |
last post by:
I have a sales table that logs sales information for each active job at the
end of each month. My question is, will the following query give me the last
sell price for each job in the sales table?
SELECT DISTINCTROW tblJobSales.Job, Last(tblJobSales.SellPrice) AS SellPrice
FROM tblJobSales WHERE (((tblJobSales.SellDate) Between #1/1/2007# And
#12/31/2007#)) GROUP BY tblJobSales.Job;
I have been using Max(tblJobSales.SellPrice), but I...
|
by: oszapped |
last post by:
I am new to creating SQL queries and need some help.
I am trying to query from three tables.
1 - A table with a list of Drawings (DRAWINGS)
2 - A table with the Revision history of the Drawings (REVISIONS)
3 - A table with the list of Projects (WRI_SITE_PRJS)
The result I am looking for:
Name of Drawing (I want to return the drawings that don't have a revision as well)
Last Drawing Revision
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
