Using SQL Query problem

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
219
Hi I've written the statement below. But it's complaining about the "HAVING" part. I'm trying to prefilter the data prior to using Power Query.

SELECT tbljournal.posted_dt AS [JOURNAL DATE],
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost AS [EXT LANDED COST],
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note
FROM (tblreceiveitem
INNER JOIN tblreceive
ON ( tblreceiveitem.source_loc_code =
tblreceive.source_loc_code )
AND ( tblreceiveitem.dest_loc_code =
tblreceive.dest_loc_code )
AND ( tblreceiveitem.vendor_code =
tblreceive.vendor_code )
AND ( tblreceiveitem.reference_no =
tblreceive.reference_no ))
INNER JOIN tbljournal
ON tblreceive.link_journal = tbljournal.link_journal
GROUP BY tbljournal.posted_dt,
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost,
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note,
tblreceiveitem.line_no
HAVING (( ( tbljournal.posted_dt ) ># 1 / 31 / 2015# ));
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,339
Office Version
2019, 2016, 2013
Platform
Windows
I'm very rusty on Access and hardly ever use GROUP and HAVING, but it occurs to me you have 17 top SELECTS and 18 GROUP BY
 

anvg

Active Member
Joined
Feb 14, 2012
Messages
478
Hi
You have a mistake with
Code:
[COLOR=#333333](( ( tbljournal.posted_dt ) ># 1 / 31 / 2015# ));[/COLOR]
It is not allowed in HAVING clause. Use
Code:
Select fileldList From JoinedTables Where [COLOR=#333333]( tbljournal.posted_dt ) >#1/31/2015# Group By groupFields[/COLOR]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,375
Messages
5,468,238
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top