Query to identify missing tax returns

[Fernando]

I keep an Access 2007 table with client names (about 20) and monthly sales
tax returns filed for each one of them. I have fields to indicate the period
(month & year) and the date it was filed. I jave a separate table
(TaxPeriods) listing all the periods (month & year) for which a return should
have been filed.

I need to create a query to identify ALL tax returns which have not been
filed for a specific client. In essence the result should be a list of
clients and the month which was been skipped.

 

[Allen Browne]

One client submits many tax returns. You therefore need 2 tables: one = a
listing of clients; the other is a listing of tax returns. The tables will
have fields like this:

tblClient:
- ClientID AutoNumber primary key
- ClientName Text


tblReturn:
- ReturnID AutoNumber primary key
- ClientID Number relates to tblClient.ClientID
- ReturnMonth Date/Time first of the month, e.g. 1/1/2009

Presumably your other table has a Date/Time field, and contains a record for
each month (i.e. the date is the first of the month.) We will assume it is
named tblMonth.

With that in place, create a query using tblClient and tblMonth. There will
be no line joining these 2 tables in the upper pane of query design. This
gives you a record for every possible combination (known as a Cartesian
Product.) Save the query as (say) qryClientMonth.

Now you can use the Unmatched Query Wizard (first dialog when you create a
new query) to give you all records in qryClientMonth that don't have a match
in tblReturn.

 

[MGFoster]

I keep an Access 2007 table with client names (about 20) and monthly sales
tax returns filed for each one of them. I have fields to indicate the period
(month & year) and the date it was filed. I jave a separate table
(TaxPeriods) listing all the periods (month & year) for which a return should
have been filed.

I need to create a query to identify ALL tax returns which have not been
filed for a specific client. In essence the result should be a list of
clients and the month which was been skipped.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT C.client_name, P.tax_period As Skipped
FROM TaxPeriods As P LEFT JOIN Clients As C
ON P.tax_period = C.tax_period
WHERE C.tax_period IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTJVDoechKqOuFEgEQJEcACePVVe0FhjDKMVkHWqH/cG3pYrEuQAoOBl
mANHQwowht+GZ79O0CxY3q1P
=xBPs
-----END PGP SIGNATURE-----

 

[Fernando]

Thanks to both for the advice. I tried Allen's method first and it worked.
Allen, just one more question. Following your instructions, I created a
ClientID field on the Returns table and linked it to the ClientID field on
the Clients table. While the ClientID field on the Clients table is the
AutoNumber field and has the automatically generated values, it remains empty
on the Returns table. What is the reasoning behind that?

Fernando

 

[Allen Browne]

Create a form (bound to tblClient), with a subform (bound to tblReturn.)
The subform control has properties named LinkMasterFields and
LinkChildFields. Set these to ClientID (i.e. the name of the primary key of
the Client table for LinkMasterFields, and the name of the matching field in
tblReturn for LinkChildFields.)

Now when you enter a new record in the subform, it will automatically
populate it with the ClientID from the main form.

BTW, I would also encourage you to:

a) Create a Relationship between the 2 tables (Relationships on Database
Tools ribbon in A2007, or on the Tools menu in earlier versions);

b) Set the Required property to Yes for ClientID in tblReturn (unless you
want to accept returns that are not identified with any client.)