Access highlights "ON" and gives me the error message
There is no “ON†if you did it the way I said.
The complete SQL as I wanted you to try is below.
Run and see if you get any errors. You should get a VERY large amount of
records. If no errors then change to design view and drag to make the joins.
Run to test. If not correct then change the join.
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt, MultiRefNum.Referencenumber
FROM JCHSTTRN, MultiRefNum
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#) AND
((JCHSTTRN.Transactionunits)=IIf([MultiRefNum].[Referencenumber] Is Not
Null,"0","1") Or (JCHSTTRN.Transactionunits)="0"))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;
:
I don't understand what you mean...what's the FROM statement?
I can't get beyond the SQL view in my queries (it won't do anything but give
me an error message when I try to run it in the design view). When I take
out the LEFT JOIN to match what you have below (FROM JCHSTTRN, MultiRefNum),
Access highlights "ON" and gives me the error message I reported yesterday:
"Syntax error in FROM clause" and won't let me switch from SQL view to Design
view.
Am I supposed to take out the rest of the line so it only reads: FROM
JCHSTTRN, MultiRefNum ??
:
I asked what was the results when you dropped the LEFT JOIN from the query as
I suggested?
Using the two tables like this ---
FROM JCHSTTRN, MultiRefNum
:
These are my two queries:
SELECT JCHSTTRN.Referencenumber, JCHSTTRN.Jcreference,
Count(JCHSTTRN.Referencenumber) AS CountRefNum
FROM JCHSTTRN
WHERE (((JCHSTTRN.Referencenumber)>"1"))
GROUP BY JCHSTTRN.Referencenumber, JCHSTTRN.Jcreference;
and
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt, MultiRefNum.Referencenumber
FROM JCHSTTRN LEFT JOIN MultiRefNum ON JCHSTTRN.Referencenumber =
MultiRefNum.Referencenumber
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#) AND
((JCHSTTRN.Transactionunits)=IIf([MultiRefNum].[Referencenumber] Is Not
Null,"0","1") Or (JCHSTTRN.Transactionunits)="0"))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;
:
Post what you have in the FROM statement.
:
I get an error message that says "Syntax error in FROM clause"
:
I've worked all day on it, trying to come up with something and I got
nothin'
I can not make any suggestions with that kind of comment.
What was the results when you dropped the LEFT JOIN from the query as I
suggested?
:
I'm still having a problem getting this to work!
I've worked all day on
it, trying to come up with something and I got nothin'
:
That error does not always mean 2 different types.
Try editing SQL from this --
FROM JCHSTTRN LEFT JOIN MultiRefNum ON JCHSTTRN.Referencenumber =
MultiRefNum.Referencenumber
WHERE (((JCHSTTRN.Jcreference) ......
to this --
FROM JCHSTTRN, MultiRefNum
WHERE (((JCHSTTRN.Jcreference) .....
Then try to run it. You will get a WHOLE LOT of records. If it runs
without error then complete the join in design view. Try running again.
:
I got the MultiRefNum query to work, but when I paste the info for the join
query into SQL, I'm getting an error upon running that says, "Data type
mismatch in criteria expression". I know that means somewhere, there is the
same data field but 2 different types. How do I figure out where and what?
:
I posted eariler but to wrong thread.
Name this totals query MultiRefNum --
SELECT JCHSTTRN.Referencenumber, Count(JCHSTTRN.Referencenumber) AS
CountRefNum
FROM JCHSTTRN
WHERE Count(JCHSTTRN.Referencenumber) >1
GROUP BY JCHSTTRN.Referencenumber;
Your query joined with the query above --
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt, MultiRefNum.Referencenumber
FROM JCHSTTRN LEFT JOIN MultiRefNum ON JCHSTTRN.Referencenumber =
MultiRefNum.Referencenumber
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#) AND
((JCHSTTRN.Transactionunits)=IIf([MultiRefNum].[Referencenumber] Is Not
Null,"0","1") Or (JCHSTTRN.Transactionunits)="0"))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;
:
SELECT DISTINCTROW JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate, JCHSTTRN.Unittype, JCHSTTRN.Description,
JCHSTTRN.Referencenumber, JCHSTTRN.Documentnumber, JCHSTTRN.Transactionunits,
JCHSTTRN.Amt
FROM JCHSTTRN
WHERE (((JCHSTTRN.Jcreference) Not Like "*??????????3*" And
(JCHSTTRN.Jcreference) Not Like "??????????4") AND
((JCHSTTRN.Transactiondate)>=#1/1/2008#))
ORDER BY JCHSTTRN.Jcreference, JCHSTTRN.Jctransactiontype,
JCHSTTRN.Transactiondate;
Is this what you meant? Thanks for your help!
:
Ok, if you will post your report query I will edit it so you only get the one
record.
:
Both records are being pulled from the same table. I'm actually using a
query for the report to narrow the information to a date range of 1/1/2008 to
present.
There is a field that distinguishes them...Transactionunit. It is a '1' for
the voucher and a '0' for the AP check.
:
Does the report pull records from two tables are both records in one table?
If in one table are there other fields to distinguish them?
:
When we enter a voucher into our accounting system and enter the
corresponding project number, we create a record in Accounts Payable and a
record for that project in the Project Costing module. When we cut the AP
check, there is a second record created for the project. When the user runs
the project costing report, we get a report that looks like this:
Document Nr. Reference Nr. Date Description Amount
Where the document number is the AP check number and the reference number is
the voucher number. However, unless manually deleted from the system, the
voucher line item will also show on the report with the document and
reference numbers both showing the voucher number. For example,
Document Nr. Reference Nr. Date Description Amount
0109785 0109785 11/1/08 freight out 257.84 (this is
the voucher)
0050154 0109785 11/21/08 ABC Freight 257.84 (this is
the AP check)
On my Access Report (I have Access 2003), I would like to eliminate the
voucher line item and ONLY show the check (of course, only if the voucher has
been paid – this isn’t an issue if the voucher is unpaid). How would I
correctly used the Hide Duplicates property in the report or a Union query
(from my understanding, the Union query is designed to eliminate duplicate
records). I know I’d need to tell the report to compare the document and
reference numbers, but I’m not sure which is the best way to go.
Thank you!
Heather
