Re: MERGING TWO QUERIES

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 12:16:42 -0400

SELECT [Maintenance Purchases].TruckID AS TruckID, Sum([Maintenance
Purchases].Amount) AS Amount, Sum(Mileage.Miles) AS Miles,
Format([InvoiceDate],"mmm") AS [Month],
DatePart("yyyy",[InvoiceDate]) AS [Year]
FROM [Maintenance Purchases]
WHERE (((DatePart("yyyy",[InvoiceDate])) Between 2003 And 2004))
GROUP BY [Maintenance Purchases].TruckID, Format([InvoiceDate],"mmm"),
DatePart("yyyy",[InvoiceDate]), DatePart("m",[InvoiceDate])
HAVING ((([Maintenance Purchases].TruckID)="00-66"))
ORDER BY [Maintenace Purchases].TruckID, DatePart("yyyy",[InvoiceDate]),
DatePart("m",[InvoiceDate]); 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Kelli McCann" <KelliMcCann@discussions.microsoft.com> wrote in message
news:1024ADBE-32E6-49DE-8E2A-B04A811B46EE@microsoft.com...
> Hello,
>
> Below I will post my two queries and the SQL view. I am wondering if these
> two queries could actually just be one query. The information comes from
two
> different tables. If this is possible could someone please post the SQL
view
> of what the query would look like because I am having some difficulties.
>
> COST PER MONTH - Query:
>
> SELECT [Maintenance Purchases].TruckID AS TruckID, Sum([Maintenance
> Purchases].Amount) AS Amount, Format([InvoiceDate],"mmm") AS [Month],
> DatePart("yyyy",[InvoiceDate]) AS [Year]
> FROM [Maintenance Purchases]
> WHERE (((DatePart("yyyy",[InvoiceDate])) Between 2003 And 2004))
> GROUP BY [Maintenance Purchases].TruckID, Format([InvoiceDate],"mmm"),
> DatePart("yyyy",[InvoiceDate]), DatePart("m",[InvoiceDate])
> HAVING ((([Maintenance Purchases].TruckID)="00-66"))
> ORDER BY [Maintenace Purchases].TruckID, DatePart("yyyy",[InvoiceDate]),
> DatePart("m",[InvoiceDate]);
>
>
> MILES PER MONTH - Query:
>
> SELECT Mileage.TruckID AS TruckID, Sum(Mileage.Miles) AS Miles,
> Format([MilesDate],"mmm") AS [Month], DatePart("yyyy",[MilesDate]) AS
[Year]
> FROM Mileage
> WHERE (((DatePart("yyyy",[MilesDate])) Between 2003 And 2004))
> GROUP BY Mileage.TruckID, Format([MilesDate],"mmm"),
> DatePart("yyyy",[MilesDate]), DatePart("m",[MilesDate])
> HAVING (((Mileage.TruckID)="00-66"))
> ORDER BY Mileage.TruckID, DatePart("yyyy",[MilesDate]),
> DatePart("m",[MilesDate]);
>
>
> Any help would be great. Thanks. Kelli
>
>

Relevant Pages

  • Re: MERGING TWO QUERIES
    ... I misread your queries, and thought they were both based on ... 3rd query that joins the 2 together. ... >> SELECT [Maintenance Purchases].TruckID AS TruckID, ... >> FROM [Maintenance Purchases] ...
    (microsoft.public.access.queries)
  • Re: MILES ARE NOT SHOWING
    ... "Kelli McCann" wrote in message ... the miles aren't there because the query isn't ... Since [Maintenance Purchases] does not ... In order to combine the two queries, ...
    (microsoft.public.access.queries)
  • Re: Creating Cost Per Month
    ... Below is the SQL of my query: ... FROM [Maintenance Purchases] LEFT JOIN Mileage ON [Maintenance ... > Kelli McCann wrote: ... I'm not sure what you mean by can I post the SQL ...
    (microsoft.public.access.queries)
  • AMOUNTS KEEP CHANGING
    ... I was having a problem with the miles not showing up. ... because I didn't have [Mileage] in the from clause also. ... FROM [Maintenance Purchases], ... But then when I run my query my amouts and mileage are really large amounts. ...
    (microsoft.public.access.queries)