Two more newbie Access questions

I’m still an Access newbie, and need some help with what should be some basic things.

I have a table with all of my transactions broken down categorically (client name, date, haircut price, blowdry price, etc.). I also have check box fields for ‘jar money’ and ‘report to IRS?’ for each transaction.

Question 1: How do I make a query that will let me know the amount I earned, with the report to IRS button checked, from 10/16/2002 to 10/22/2002?

Question 2: I do my books weekly. At the end of inputting transactions, I have a query/report that shows me the total I made, the total I tell the IRS I made, and the total Jar Money (which is a seperate self-imposed “forced savings” account). Is there any way to have these three numbers automatically entered in an Excel spreadsheet, week by week, so at the end of the year, I will have a list of 52 weekly numbers (and 3 columns)? Now I am doing this manually.

Thanks in advance for your help.

Seth

Is each record either ‘jar money’ or ‘report to IRS’ (i.e. never both or neither)? - if so then you only need one of the fields.

Q1: In the query design screen, add your table and drag the date, IRS and value fields down into the grid, in the criteria row below the transaction date field, type >=#10/16/2002# AND <=#10/22/2002 - in the criteria row below the IRS field, type True

That query will just find all the records, to add them up, right-click on the query grid and select Totals; a new row will appear, change the expression under date and IRS to read Where and the expression under value to read Sum; run the query and that should be that.

Q2 should be possible by exporting a crosstab query as an excel sheet, but I’ll have to think it through a little more.

Silly question; I don’t really understand the US tax system - when you talk about keeping track of ‘jar money’ and ‘Report to IRS’, you’re not describing anything <ahem> improper or illegal are you? (not that I’d dream of trying to report you or anything, it’s just not a terribly wise idea to set up a system that keeps accurate track of improper business practices)

Oops, add a # after that second date criteria (Access will put these in for you anyway, I think).

There can be both or neither - they are independent.

Yes, the database is designed to keep two sets of books - it isn’t for me, it is for a friend. While she uses the “don’t report to IRS” feature sparingly, it is rampant in the hairdressing business. Since she (and others) already do it, I made the database to at least speed the process of bookkeeping (and minimize math errors). There isn’t anything in the database that a quick glance at the discrepancy between bank deposits and the written out books shows.

I think that any automated thing you could think up would be more trouble that it’s worth.

You could make a report that has all the fields you want, but make them all running total fields. You could group the report by week number. At the end of the year, you’d have one report with all 52 sets of numbers. You could export this file to Excel or simply type it out again.