Report created from Query based on 2 Tables - Invoice and Cases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2002 Win o/s XP
I created a report of monthy cash receipts based on my invoice table.
The invoice table is joined to the parent table by a case number field.
I need the name of the client to appear on the report, but when I try to add
the cases table for the field that contains the client name, I get duplicates
and triplicates of the records (each record is a case).
Please help. Thanks.
 
JoanOC,

I would look at your joins, you probably need to join some more fields. I
would help to know the structure of both of your tables in order to fully
answer your question.
 
The following are the queries:sure how to copy the structures. Hope this will
help you help me. Thanks for reply.

Cases SQL Query:
SELECT Cases.RS_FILE_NO, Cases.D_O_L, Format$([D_O_L],"mm\/d\/yy") AS
LossDate, Format$([HEARING DATE],"mmmm d"", ""yyyy") AS [Date],
Format$([HEARTIME],"h:nn AM/PM") AS [Time], Cases.CLAIMANT, Cases.CLAIMANTFN,
Cases.INSURED, Cases.INSUREDFN, Cases.FILE_ID, Attny.FIRSTNAME1,
Attny.LASTNAME1, Firm.FIRM_NAME, Firm.ADDRESS3, Firm.ADDRESS4, Firm.CITY1,
Firm.STATE1, Firm.ZIP_CODE1, Firm.PHONE1, Firm.FAX1, Contacts.PREFIX,
Contacts.FIRSTNAME, Contacts.LASTNAME, Contacts.PHONE, Contacts.EXT,
Contacts.FAX, Carriers.NAME_CO, Carriers.ADDRESS1, Carriers.ADDRESS2,
Carriers.CITY, Carriers.STATE, Carriers.ZIP_CODE, D_Attny.D_FIRSTNAM,
D_Attny.D_LASTNAME, Def_Firm.D_FIRM, Def_Firm.D_ADDRESS3,
Def_Firm.D_ADDRESS4, Def_Firm.D_CITY1, Def_Firm.D_STATE1,
Def_Firm.D_ZIPCODE1, Def_Firm.D_PHONE1, D_Attny.DPHONE, Def_Firm.D_FAX1,
Cases.REP_ID, Cases.ATTNY_ID, Cases.NEUTRAL_ID, Cases.DEF_ATTNY,
Cases.CODE_NAME, Cases.CODE_FIRM, Cases.CODE_DFIRM, Cases.CODE_NFIRM,
Cases.SUB_DATE, Cases.STATUSDATE, Cases.SUB_BY, Update.CODE_NO,
Update.SHORT_DES, Update.STATUS, Update.STATID, N_Attny.N_FIRSTNAM,
N_Attny.N_LASTNAME, N_Firm.NFIRM_NAME, N_Firm.N_ADDRESS3, N_Firm.N_ADDRESS4,
N_Firm.N_STATE1, N_Firm.N_CITY1, N_Firm.N_ZIPCODE1, N_Firm.N_PHONE1,
N_Firm.N_FAX1, Cases.CHOICE_PRO, Cases.HIGH, Cases.LOW, Cases.SETTLEMENT,
Cases.INSURED_POS, Cases.PLAINTIF_POS, Cases.DEMAND, Cases.OFFER,
Contacts.UNIT, Cases.ACTIVE, Cases.BLOCK, Cases.AGE, Cases.RD, Cases.MP,
Cases.[PARTY#], Cases.CODE_NO, Cases.SUBNOTES2, Cases.SUBNOTES1,
Cases.TICKLE, Cases.LA, Cases.HEARPLACE, Cases.ADDRESS9, Cases.CITY4,
Cases.STATE4, Cases.ZIP_CODE4, Cases.PHONE4, Cases.[HEARING DATE],
Cases.HEARTIME, Cases.Award, Cases.Paid, Contacts.FIRSTNAME_1,
Contacts.LASTNAME_1, Attny.P_SECT, Attny.EXT1, Cases.Schedule,
Cases.Schedule2, Cases.Schedule3, Cases.Schedule1, Cases.[Schedule Notes],
Cases.SETTLEMENT2, Cases.SUBNOTES1, Cases.[Date Paid HO], Cases.[Amt Paid
HO], D_Attny.[D_Attny Email], N_Attny.N_Email, Cases.MD, Attny.[EMAIL Attny],
Contacts.[Email Rep]
FROM N_Firm RIGHT JOIN (N_Attny RIGHT JOIN ([Update] INNER JOIN (Carriers
RIGHT JOIN (Firm RIGHT JOIN (Def_Firm RIGHT JOIN (D_Attny RIGHT JOIN
(Contacts RIGHT JOIN (Attny RIGHT JOIN Cases ON Attny.ATTNY_ID =
Cases.ATTNY_ID) ON Contacts.REP_ID = Cases.REP_ID) ON D_Attny.DEF_ATTNY =
Cases.DEF_ATTNY) ON Def_Firm.CODE_DFIRM = Cases.CODE_DFIRM) ON Firm.CODE_FIRM
= Cases.CODE_FIRM) ON Carriers.CODE_NAME = Cases.CODE_NAME) ON Update.CODE_NO
= Cases.CODE_NO) ON N_Attny.NEUTRAL_ID = Cases.NEUTRAL_ID) ON
N_Firm.CODE_NFIRM = Cases.CODE_NFIRM;

Invoice SQL Query:
SELECT Invoice.[Invoice ID], Invoice.RS_FILE_NO, Invoice.REP_ID,
Invoice.ATTNY_ID, Invoice.DEF_ATTNY, Invoice.AMT_PRO_FE, Invoice.DATE_BILL,
Invoice.AMT_RECD, Invoice.DATE_RECD
FROM Invoice
WHERE (((Invoice.DATE_BILL) Between [1st date] And [Last date]));
 
Back
Top