pulling information from access

  • Thread starter Thread starter lioneyes
  • Start date Start date
L

lioneyes

Hi. Thanks in advance for your help.

Say I have a table with fields called 'CO#' 'PAYDATE' 'PAYMENT#' 'AMOUNT'.

The 'PAYMENT#' field is the payment number, is very important, and it typed
in not autonumber (on purpose).

So for each 'CO#' (each company) there are many payments, each numbered and
dated.

I want to pull a report for any company ('CO#') that has less than 26
payments.

How can I do this?

Also, if I want to pull a report for each company that does not have a
record with payment # 1 for instance.

Trying to find an easier way to pull all records only for companies that are
missing payments basically.

Thanks very much.
 
Also wanted to add that I have another table with the CO# and address data
etc. that is related based on the CO# field. If that needs to be used
 
So I'm thinking that I have to write some VB (I'm going to attach to a report
button for others to use) that Counts the number of payment records for each
Co# and then tell it to pull the payment report (shows all payment records
sorted by Co#) only for those CO# that have less than 26. But I don't know
how to write that code. Does that make sense or is there a better way?

If I was to pull the payment report for CO# that is missing a record with
payment #1 in PAYMENT#, and then pull all payment records only for those
companies missing that payment ....that is the other thing I'd like to be
able to do. Not sure how to attack that one at all.

Thanks.
 
The first thing to do is rename all your fields that have # in them.
Field names should contain only numbers, letters, and the underscore
character. No special characters or spaces should be use at all.

This has to do more with a query than the report itself. You need to create
a query as the report's record source that includes the information you need
with the criteria to return the rows that meet your business rules. In this
case, a Totals query with a count of PaymentNumber < 26 would probably be
the case.
 
Thanks for that info Klatuu. I will heed your advice about taking the # out
of the field names.

Will the totals query for payment count <26 give me each record for the
companies with less than 26 payments or only the total of the payment amount
field for those companies? I do need to see each record, not just the total
of the payment amount field.

Also, any ideas about pulling for one missing only a specific payment number
because in some cases having less than 26 payments is valid.

thanks.
 
You can use the totals query as a subquery to return all the records for the
company.
 
Yes, thank you. I ended up figuring that out too.

Any idea how I can pull all payments but only for a company missing a
specific payment(by payment number)? I'm figuring I will need VB code to do
that but not sure.

Thanks again for your help.
 
First identify all the companies that have that payment
Second use that in an unmatched query (wizard available) to get those that do
not have the payment.
Third use that to get all the records for those unmatched companies.

Or use NOT In and a subquery.
SELECT *
FROM [YourTable]
WHERE [CO#] NOT IN
(SELECT [CO#]
FROM [YourTable]
WHERE [Payment#] = 15)

That is likely to be slow. If your Field and Table names follow the naming
conventions:
-- Start with a letter
-- Consist of only letters, numbers, and the underscore
-- Are NOT reserved words (e.g., Name, Date, Time, Parameters, Select)
you can build a query in one step that is faster. Since you do have # in your
field names you will have to use a two step method

Query one - Saved as qHas15
SELECT [CO#]
FROM [YourTable]
WHERE [Payment#] = 15

Query two - use your table and the saved query as sources for the unmatched
query wizard which will build a query like:

SELECT YourTable.*
FROM YourTable LEFT JOIN qHas15
ON YourTable.[CO#] = qHas15.[CO#]
WHERE qHas15.[CO#] is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top