Create a statement query or report

I have a database with two tables. One is Project and has a ProjectID, 
ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID, Date 
and Amt.

How do I combine these two to make a statement with the data sorted by date?

Thanks,

Bill 


0
Bill
1/25/2008 1:53:23 AM
access 16762 articles. 3 followers. Follow

2 Replies
611 Views

Similar Articles

[PageSpeed] 3


"Bill Allen" wrote:

> I have a database with two tables. One is Project and has a ProjectID, 
> ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID, Date 
> and Amt.
> 
> How do I combine these two to make a statement with the data sorted by date?
> 
> Thanks,
> 
> Bill 
> 
> 
> 
Start with a query.  Place all of the required info into the query and add a 
calculated field using the DateDiff() function.
If the DateDiff() is 30 days from the billing date, you can issue a 30 day 
statement, 60 for a 60 day statement etc.
For this example we'll call your calculated field PastDue30:([InvAmount])
In the criteria use DateDiff("d",30,Now())
I would set up a second report to indicate how many accounts are past due 
and print that first as a checklist.
0
Utf
1/25/2008 3:13:01 AM
How do I account for payments on the account?

For example, here are the results of an "Invoice Query by selected project"
  Query1 ProjectNo InvDate InvAmt
      20714 12/31/2007 $4,032.25
      20714 8/11/2007 $1,500.00
      20714 11/26/2007 $2,840.06
      20714 7/16/2007 $3,137.66
      20714 8/17/2007 $3,000.00
      20714 10/22/2007 $10,289.01
      20714 1/8/2008 $1,418.75
      20714 1/4/2008 $58.00


Here are the receipts for the same project:

  Query1 ProjectNo Date Amt
      20714 8/18/2007 $1,500.00
      20714 12/7/2007 $2,840.06
      20714 6/4/2007 $2,000.00
      20714 7/27/2007 $1,137.66
      20714 8/17/2007 $3,000.00
      20714 12/7/2007 $9.94
      20714 12/28/2007 $1,000.00
      20714 1/2/2008 $1,000.00
      20714 1/3/2008 $1,000.00
      20714 1/4/2008 $1,000.00
      20714 1/7/2008 $1,000.00
      20714 1/8/2008 $1,000.00
      20714 1/9/2008 $1,000.00
      20714 1/11/2008 $1,000.00
      20714 1/17/2008 $1,000.00


I would like a list with both invoice amounts and receipts sorted by date 
with a running balance.

I have a ProjectID key which is a one to many relationship.

Regards,

Bill

"Olduke" <Olduke@discussions.microsoft.com> wrote in message 
news:B11C0FE2-72DF-4798-B6B5-43685E0AD740@microsoft.com...
>
>
> "Bill Allen" wrote:
>
>> I have a database with two tables. One is Project and has a ProjectID,
>> ProjectNo, InvDate and InvAmt. Another table is ROA and has ProjectID, 
>> Date
>> and Amt.
>>
>> How do I combine these two to make a statement with the data sorted by 
>> date?
>>
>> Thanks,
>>
>> Bill
>>
>>
>>
> Start with a query.  Place all of the required info into the query and add 
> a
> calculated field using the DateDiff() function.
> If the DateDiff() is 30 days from the billing date, you can issue a 30 day
> statement, 60 for a 60 day statement etc.
> For this example we'll call your calculated field PastDue30:([InvAmount])
> In the criteria use DateDiff("d",30,Now())
> I would set up a second report to indicate how many accounts are past due
> and print that first as a checklist. 


0
Bill
1/26/2008 2:28:23 AM
Reply:

Similar Artilces:

Exchange 2000 Can't create a new user
I have attempted to add a new user to the exchange, which on the face of it worked. I then tried to open Outlook and run the mail setup wizard, where upon I get a message saying that the name does not appear on the Address List. Any ideas please? Troubleshoot your Recipient Update Service in Exchange System Manager. It sounds like the RUS hasn't stamped the mailbox with the right bits. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Phil" <phil.newton@prolific-ifa.co.uk> wrote in message news:2914201c46504$04d021...

Access Query
I have linked an Access query into the first tab in Excel. However, in the second tab I am experiencing problems. I can not seem to link an Access query that has some SQL code in the criteria line (the code is bascially from the wizard that identifies duplicate records from a table). Does this custom SQL in my Access query prevent me from linking this query to Excel? hi, you are a little vague but i think so. If you are getting a error like "too few parameters , expected x" you will have to remove the criteria from the access query and move it to the excel side. the error i...

Custom Report
I keep a file with sales and totals of each sale on a daily basis. At the end of each month we run a report showing the totals for that month. I have a file in Microsft Works Database that shows monthly totals. How would I make a report in Excel to Show monthly totals? You could use a myriad of ways, but my suggestion would be either subtotals (Data>Subtotals...) or a pivot table (Data>Pivot table report or chart) Post back if you need help with these -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "ClearColour" &l...

Create a "turn off the print prompt" option
When printing anything from GP you receive a printer prompt for copies after the prompt for destination. You can set up default printers and such for reports, however, you cannot get rid of all of the prompts. In some settings you need to be prompted for options of printing. But, the number of clicks to say ok I want to print this and accept defaults sometimes are 3 or more. This would be nice if a one click accepted everything you just printed. Or better yet, you combine the where to print with the printer dialog box, one screen all options. This combining of screens and minimizi...

Creating formula #2
How would I do this (in Excel 2007)? I have 2 columns of numbers. I would like to divide column B by column A and put the result in the next, blank column over. And then do that for each line. I have no clue. How would I do that? Thanks. Maybe, you can fill that third column with a bunch of formulas: =b1/a1 Or to avoid errors if A1 is 0: =if(a1=0,"",b1/a1) JohnB wrote: > > How would I do this (in Excel 2007)? > > I have 2 columns of numbers. I would like to divide column B by column A > and put the result in the next, blank column over. And then do t...

Macro to create multiple charts?
Hi, I have a bunch of excel files full of data, and each one contains information which needs to be made into 30 charts (scatterplots). I have been hand selecting the data and then creating each chart manually, but I am hoping there is a way to automatize the process. Basically what I need to do is this: 3 adjacent columns of data look down column 1 for the word "stop" (the rest are all numbers) when you get to stop, select all three columns until the next time there's a "stop" in column 1 make the scatterplot do it again from the next stop (the stop lines can j...

Access Query Help
I have a query that selects about 10 fields, one of which is called CODE. The value I'm looking for is where code = AAAA. (this is an example..) I get a return of 100 records or so. Now I want to flip this and return those records that do not have a code that equals to AAAA. I've tried Where NOT Exists (AAAA) but I get a syntax error...can some one help with the syntax when using this type of query...or am I going about it all wrong? Thanks! <> "AAAA" -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. &q...

Initiated by...... reports
OK - so 500+ contacts later, I've figured I've entered these all wrong. We have a business where people come enjoy our services in 'groups'. I have entered every person as a business contact and in the initiated by..... field have entered the 'leader' of the party that I wanted to 'link' them to. I NOW want to run a report to see who the most business has come from and who they are. Help? Thanks! "JitterbugJenn" <JitterbugJenn@discussions.microsoft.com> wrote in message news:1894D172-1013-4DC2-938D-8CE6AE26338A@microsoft.com....

CRM Report Wizard problem
seems that the CRM Report Wizard when you use seondary records hard-codes a "ModifiedOn" column filter range that limits access to these records to only within the last 30 days and the only way to make it work is to edit the xml of the report and delete the filter..... it that true? any solution? Hi Paolo, The Reports have a Edit Default Filter option available. You can change the default filter from the Modified on to any other atribute you wish. The revised filter settings are then stored with the report. HTH Sam _______________ Inogic Innovative Logic Innovative solutions...

Creating Interactive Spreadsheets with Activex Controls
I am trying to create a spreadsheet to calculate travel claims and want users to enter data such as mileage travelled I also want them to be able to select a tick box which is relevant to there engine size and thus depending the box selected a different calculation will be run on the mileage to calculate the tax. e.g selecting box one would perform the calculation {cell with mileage}*.1*7/47 while box two would perhaps be {cell with mileage}*.12*7/47 Any advice much appreciated Regards Lloyd Lloyd, Here is one way. It assumes that the choices are columns B:D, choice 1,2, and 3. To use...

Multi level BOM report in sql
I want to write a script to generate the multi level BOM report outside of Great Plains report writer. I dont know to get the components at all levels with their correct position and levels. If anyone has worked on this, I will appreciate the help. Thanks in advance haribhagat@hotmail.com ...

Report containing multiple critera/filters
This is what I need done, I've tried everything I know how, but it's not enough. I run departmental audits, of which contain multiple employees, and make multiples purchases against their accounts. I run these reports monthly, and sometimes need to revisit monthly reports where data has changed. I have theses fields, with the needed criteria for the reports next to them. department - name # of employees reviewed per dept - count employees reviewed - names listed, list preceded by "(C)" review date - none review type - if more than one, list them separated ...

Report showing address for undelivered mail
Hi I am trying to find out if there is some kind of report I can run in Outlook that will provide me with the mail address of emails that were not able to be delivered or are bouncebacks. Can anyone help please Thanks Outlook is not responsible for delivering the messages but the mail server is. If a message cannot be delivered, the mail server will send you this report. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real Wor...

reporting document amount
I have the following SQL working in SQL Reporting Services, however I need to also add in the document amount (amount of check). This data is stored on the Payables Payments Zoom screen. I just need to know the name of the underlying table and field name that stores this information. Thanks!! select CompanyID, DOCNUMBR, Checks.DOCNUMBR CheckNumber, Checks.CHEKBKID , DOCDATE CheckDate, Vendor.VENDORID, VENDNAME, VNDCHKNM,ADDRESS1, CITY, STATE, ZIPCODE, PHNUMBR1,USERDEF1, PT_UD_Key PaymentID, DATE1 CheckDeliveryDate from ECFCA.dbo.PM00400 Checks join ECFCA.dbo.PM00200 Vendor on Vendor...

create a form that collects parameters
Hi, i need a form where we search for a detail and the result should be published in the bottom, based on the result the user will decide what to do next in acess 2007. i refered the link below http://office.microsoft.com/en-us/access/HA100963141033.aspx#form_param in this some mistake is there in the macro can somebody help me out. if any template for search form is available please let me know. thank you, -- Kalai Maybe something like this: http://www.fontstuff.com/access/acctut08.htm This could be very helpful too: http://www.fontstuff.com/access/acctut06.htm Al...

Access 2007 Report Export To TXT Line Wrapping...
Hi, I have a very simple problem to describe to which I cannot find a solution: I have a report with each field EXACTLY 120 characters long, which I need to export to a TXT file to then import into a bank software... In Access, I can play with the font size and have each field on one line but, when I export it to the TXT file, the fields break down to the next line around 70/80 characters... Is there a way to force the field to stay on one line in the resulting TXT file? Thank you in advance for any help you may provide, Lupo What application are you using to view the data i...

Creating an"Average" Line in a Scatter Graph
I have created a scatter diagram from two columns of data. How do I superimpose or generate a glide slope line that represents the average scatter density? Thanks If you mean a trendline, Chart menu, and Add Trendline. -- David Biddulph "Chris Fearon" <ChrisFearon@discussions.microsoft.com> wrote in message news:31F41FFB-DD72-40ED-92F3-F6F0321AAE5C@microsoft.com... >I have created a scatter diagram from two columns of data. How do I > superimpose or generate a glide slope line that represents the average > scatter density? Thanks See http://www.andypope.inf...

Using a Variable as a query Expression Value
Hi, I've been reading a past post "Passing a Variable As Query Criteria" Ofer - Can anyone explain this a bit further. I'm afraid I'm not very good with functions and always get a bit lost on this. The original response was: "You can create a Function that return the value of the variable, and use this function in the query Global VarName as Double Function FunctionName () as Double FunctionName = VarName End Function Both, function and variable should be defined in a module, and not in a form or report" I have done this, but how do you call the Vari...

Sales Analysis Report Last year VS this year
Does anyone have a sales analysis reports that they would be willing to share. I would like to do sales compares form last year to this year in a single report. Thanks, TomT Me Too lane.dj (at) gmail.com "TomT" <ttaylor@cherrybrook.com> wrote in message news:uK$OaQmPIHA.6036@TK2MSFTNGP03.phx.gbl... > Does anyone have a sales analysis reports that they would be willing to > share. > > I would like to do sales compares form last year to this year in a single > report. > > Thanks, > > TomT > hi Guys, I have one which I did for one co...

SRS Reports
Hi there I have installed SQL 2005 with the default Reporting services configuration. I also loaded Business Portal and Webservices. I then installed the SRS_Reports from Microsoft for each of my companies including the TWO company, and all worked fine. I could run all the reports for each company for each series about a month ago. In the last week I cannot run any report for any company, only TWO. All the other company reports show a page stating it "Cannot access the remote server". I uninstalled the SRS Reporting application and reinstalled it successfully. I then tr...

Management Report
Hi, Please can you explain to me why if MR is run on IIS why it cannot be accessed as a website and instead seems to need a MR client installed? If it is possible to run \ view reports via Internet - how do I go about doing this please? I ask as my client wishes to have MR incorporated into their Sharepoint website - please advise if this is possible and if so then how? Thank you Neil ...

Help with incorrect information from statement download
I installed money 05 and when I selected my bank money goes and contacts my bank and successfully gets my accounts information. The balance is correct, what is wrong is that it list deposits on the same side as my withdrawals. If I try to correct this manually it messes up my balances. I know the information from my bank is correct because on different computer I have been using money 03 please advise. Joshua Wickard wrote: > I installed money 05 and when I selected my bank money > goes and contacts my bank and successfully gets my > accounts information. The balance i...

Populating Sender and Recipient when creating phone call in workflow
The workflow that I have setup creates several call activities throughtout the process. The Recipient name and phone number are not auto-populated. Nor is the Sender info. How have the rest of you overcome this obstacle? ...

Pre-Posting Payroll Report
Has anyone tried to create a report that would link existing payroll transactions in batches to the payroll posting accounts to ensure that the posting accounts are correct and to validate them? I have been spending some time working on this report however; it's difficult to map the data together. If anyone has any ideas or an existing report, please let me know! Thanks! Integrity Data has done a lot of work for you. They created a number of pre-post reports that are part of their payroll to payables module. -- Charles Allen, MVP "Anonymous" wrote: > Has anyone tr...

Linking Access Query to Excel make DB Read Only
Hi, Not sure if I should post this in the Access or Excel board. I have an Access 2003 db with a select query called qryAdmitType. In Excel 2007, I selected Data => From Access. I then navigated to the Access DB and selected it. I then choose my query. My query results now appear in Excel which is what I want. The problem is that this has made my Access DB Read Only as long as the Excel file is open which I don't want. When I close the Excel file, the DB is no longer in Read Only mode. Is there a way to have both the DB and Excel file open without this happening? Thanks,...