Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / March 2007

Tip: Looking for answers? Try searching our database.

Creating a customer accounts statement vis query?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Craig - 24 Mar 2007 02:14 GMT
Hi, i use access to create invoices for jobs that i do..  currently i have
the following...

Jobs database that includes a the cost for the job.  I print an invoice from
this information...  The layout is basically the following..

job.customerID
job.description
job.date
job.charge

I also record payments in a separate database called Payments because the
payments are sometimes different than the charges...

payment.customerID
payment.date
payment.amount

I would love to create a report that uses the data from both of these tables
and generates a statement like so...

Date        Credit     Debit
1/2/07      £50.00
2/2/07                     £40.00

Total Outstanding   £10.00

I am assuming the best way is to create a query that pulls data from both
tables but even though i think i understand the concept... i dont know how i
would get access to do this... can someone help please!

thanks in advance

Craig
Ken Snell (MVP) - 24 Mar 2007 21:39 GMT
This appears to be an easy thing to do, but it actually is much more
complicated than you might first think.

You need to denormalize the charge and payment data via queries and put
those data where you can read them for your report (via another query). You
need to get a list of the dates that correspond to the payments and charges
(leaving out all other dates) and put them where you can read them for your
report (via a query). You then must combine these data into the "statement"
appearance that you want in the report.

Although it's possible to do all this by just queries, the report will
run/view/print very slowly if you have more than just a few data items; I
state this from personal experience in an application that I've written that
does statements. Therefore, I found that the best method is to use temporary
tables to hold the denormalized data and the date data, where I can index
the field(s) in the tables as needed to make the final query run faster, and
then use those tables in the final query to make the report.

If you want to pursue this, post back and we'll walk through it one step at
a time. It'll take a number of post exchanges to finish it up.
Signature


       Ken Snell
<MS ACCESS MVP>

> Hi, i use access to create invoices for jobs that i do..  currently i have
> the following...
[quoted text clipped - 30 lines]
>
> Craig
Avaya - 28 Mar 2007 00:12 GMT
Hi Ken,
Can you walk me through the step in creating a customer accounts statement
based on the following table structure?  Thank you.

tblSuppliers:
VdrID PK
Vendor

tblOrders:
VdrID FK
POnbr PK
PO Amount

tblPO Change
POnbr FK
Date Amended
Increase Amt

tblInvoice
POnbr FK
VdrID PK
InvNbr PK
InvDate
InvAmt

tblPayments
VdrID FK
InvNbr FK
DatePaid
AmtPaid

I need to calculate the balance of each suppliers open invoice and also the
available balance of each PO amount.

> This appears to be an easy thing to do, but it actually is much more
> complicated than you might first think.
[quoted text clipped - 50 lines]
> >
> > Craig
Ken Snell (MVP) - 29 Mar 2007 02:16 GMT
I'll need more detailed explanation of what the data fields are holding as a
start, and what is meant by "open" for an invoice, and whether the statement
is to be for a single PO or for all POs for that supplier.
Signature


       Ken Snell
<MS ACCESS MVP>

> Hi Ken,
> Can you walk me through the step in creating a customer accounts statement
[quoted text clipped - 97 lines]
>> >
>> > Craig
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.