Date Criteria messing up my query

[Dais]

Hello everyone.
I have made a query using two of my tables. One table only has an account number and account name. The other has all the extra stuff like date creditAmount debitAmount etc. The tables are one to many with referential ticked. All of the rest of my database works fine. I am trying to add a new piece and made a query from the above two tables which drags the account name, account number, date, debitAmount and creditAmount, The account name can have many transactions in it with all sorts of dates.
When I make my query it totals the debitAmount just fine and the creditAmount just fine also. It also includes the entire database as just account totals ( which within reason is what I want). However, as soon as I put in the date field and enter a 'start' 'finish' criteria the whole things goes silly. If I run the query it shows the total debitAmount on one line for June, the total creditAmount for June on another and the same for July etc.
I am actually trying to get the debits and credits for my 'start' 'finish' dates to show as a total either as a difference figure, or even seperate so long as I just get one line of totals for each account regardless of the number of entries for it.
Yeah, I know, you can tell I am pretty new at this but I have got a long way without asking too many questions so perhaps someone can set me along the right path to what I am doing wrong here.
I have tried a make table query and am getting the same results as doing a select query.
Any help would be so nice
Thanks all
Dais

 

[ajetrumpet]

In all honesty, because you're doing accounting work here, I think you should put your data into Excel and work with it that way. A journal type setup like the one you have described should not be done in Access anyway. I've never done accounting work in Access anyway...

Not the right program for most of it...

This may also provide some insight: http://www.access-programmers.co.uk/forums/showthread.php?t=135763

 

[Rich]

If you've never done accounting in Access why tell somebody it's not the right programme for it?

Use a Totals Query and toggle the Unique Records/Values properties

 

[ajetrumpet]

If you've never done accounting in Access why tell somebody it's not the right programme for it?

Because I'm an accountant you moron. I know what programs work well.

Lets let the other guy decide though, huh? I think he/she knows what's best...

 

[Rich]

Because I'm an accountant you moron. I know what programs work well.

Lets let the other guy decide though, huh? I think he/she knows what's best...

Well you moron I've used Access to produce my accounts for my accountant for over ten yrs and he's so impressed with the results I pay him less than other tradesmen he has in the same field, yeah Access must be the wrong programme, how odd that having discovered the power of Access I moved on to it from a spreadsheet

 

[Dais]

Hi folks
Didn't mean to start any arguments over it. When I read the garbage 3 or 4 weeks ago on various programs to use, what was easiest etc, Access was right up the top by all sorts of supposedly renowened experts so that is why I chose to do it this way. And there was NEVER a mention of don't try Accounting work.
Anyway, my accounting that I am doing here is just sooooooooooooooooo basic. It is NOT intended to be giving ratios of this versus that. It is just enter a debit on a nice form and enter a credit on a nice form and you can't get out till it balances. Pretty simple huh.
I will just plod along and keep playing with my queries. I am sure it's in there somewhere.
Thanks for the comments
Dai

 

[Rich]

Don't give up, Access can do much more than some people give it credit for

 

[Dais]

Me again
Got it all sorted - using an extra query -maybe a bit mickey mouse but it works and right now after such a very short time using Access I am quite happy. Maybe later I will go back and fine tune things.
Dai