Hello,
I have two tables that have a relationship based on a customer ID. The first table Customer has:
Customer ID
Name
Address
etc
Accounts Table has:
Customer ID
Sale
Payment
Balance
Date
Notes
etc
The Accounts table has all the transactions for all sales. So for customer 116, I could have 5 entries for sale, payment and balance.
I need to build a query for a report that looks at the table and does the following
Sort by customer name
Show the last balance for the customer based on the last or most recent date to the current date.
I know how to link tables, but I would need guidance on where to put this code if I can do this.
Thanks
2 1835 MMcCarthy 14,534
Recognized Expert Moderator MVP
Open a new query in design view. Don't add any tables then switch the view to sql. Copy and paste in the code below. Check the table names as I assumed Customer and Accounts. Also checkthe field names are correct. Then save and run the query.
SELECT C.Name, A.Sale, A.Payment, A.Balance, Max(A.[Date]) As LastDate FROM Customer C INNER JOIN Accounts A ON C.CustomerID=A. CustomerID GROUP BY C.Name, A.Sale, A.Payment, A.Balance;
Once this query is done you will be able to view it in access.
NeoPa 32,564
Recognized Expert Moderator MVP
If you want the items associated with the latest date (Payment; Balance; etc) you will need a subquery (at least that's the only way I can think of that you can).
Subqueries always look complicated if done within SQL but they needn't be if the basic structure is understood.
The simplest form of subquery in SQL is :- - SELECT subQ.* FROM (SELECT * FROM DataSet) AS subQ
Which is equivalent to
Anyway, another easier way to do it is to create another query, saved in the database like any other. I always cheat anyway and design it in the graphic window then copy / paste it into the SQL where it's required.
For this problem I think you need a subquery going something like :- - SELECT [Customer ID], Sale, Max([Date]) AS LastDate
-
FROM Accounts
-
GROUP BY [Customer ID], Sale
The main query should have an INNER JOIN link from Accounts to this subquery to select only those records which match the last date.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ridimz |
last post by:
When use ifstream, how do I ignore the last line of a file if it doesn't
contain any information?
Thanks in advance,
Ridimz
|
by: Alex Ayzin |
last post by:
Hi,
I have a problem that might be easy to solve(possibly, I've just overlooked
an easy solution). Here we go:
I have a dataset with 2 datatables in it. Now, I need to do the following:
if ds.table(0).rows(0).item("col1") = ds.table(1).rows(0).item("col2") then
txtResult.text = ds.table(1).rows(0).item("col3")
|
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are...
|
by: Richard |
last post by:
Hi,
I have a form that take some time to load due to many comboboxes and at
least 8 subforms.
When I filter or sort the main form I get an error message and then Access
shuts down. They ask if I want to send the error report to Microsoft. Has
anybody seen this type of error message and what can I do to prevent it from
happening. Am I doing...
|
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the...
| |
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: |
last post by:
Hello,
Does anyone have an idea on how I can filter the data in the gridview
control that was returned by an sql query?
I have a gridview that works fine when I populate it with data. Now I want
to look at that data and filter it based on what is in it.
I know that this could have been done with data sets and data views in
asp.net 1.1...
|
by: mkarki2 |
last post by:
I am doing a likned list program in C.
I am not really good at it and just started. But I am stuck how to pass structure in the function correctly.
this is the driver file
#ifndef DRIVER_C
#define DRIVER_C
#include "constants.c"
int main(void)
{
|
by: panjap |
last post by:
hi, i was wondering could anyone help me with a query i have created. i have sucessfully worked out the total amount od prodcuts sold in a shop.
However i want a query to show the total sold of one product only in the last month.
First i made a query which summed up the total prodcuts sold, via the qty on order.
FIELD ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
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...
|
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: 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...
| |