Clean Month & Year to Date Qry

[MoeIndustries]

Hello ALL,

I have the below financial data showing each acounts monthly figures:

Period.....Account....Monthly£
01 ........1000 ........£500
01 ........1005 ........£40
01 ........1011 ........£1,200

02 ........1000 ........£20
02 ........1005 ........£100
02 ........1011 ........£20

03 ........1000 ........£500
03 ........1005 ........£40
03 ........1011 ........£15

I want to write a query which will give me this table but also add a year to date column which will simply add, by account, all the months <= the corrisponding month i.e.:

Period ...Account.... Monthly£ .....YTD£
01 ........1000 ........£500 ...........£500
01 ........1005 ........£40 .............£40
01 ........1011 ........£1,200 ........£1,200

02 ........1000 ........£20 ............£520
02 ........1005 ........£100 ...........£140
02 ........1011 ........£20 ............£1,220

03 ........1000 ........£500 ..........£1,020
03 ........1005 ........£40 ............£180
03 ........1011 ........£15 ............£1,235

Please help!!!

 

[vbaInet]

One of:

YTD: DSum("[Monthly£]", "[COLOR=Red]TableName[/COLOR]", "Period <= " & Period & " AND Account = " & Account)

YTD: (SELECT Sum("[Monthly£]") FROM [COLOR=Red]TableName[/COLOR] As T WHERE T.Period <= [COLOR=Red]TableName[/COLOR].Period AND T.Account = [COLOR=Red]TableName[/COLOR].Account)

Amend the red bits.

 

[RainLover]

This is something I have used. You will need to change Field Name and Table name to suit your structure.

SELECT Orders.EmployeeID AS EmpAlias, Orders.Freight AS Freito, Format(DSum("Freight","Orders","[EmployeeID]<= " & [EmpAlias])-[Freito],"Currency") AS RunTot, [RunTot]+[Freito] AS Balance
FROM Orders;

 

[RainLover]

vbaInet is quick.

Now you have a choice.

Post back if you have problems.

 

[MoeIndustries]

Thank you for your help so far!

I tried the first suggestion as it seemed the most straight forward (for my level) but I keep getting the "data type mismatch" error.

Amount is number
Period is number
Account is text

 

[vbaInet]

Why is Account Text?

 

[MoeIndustries]

I've converted everything to number and it works! Thank you very much!!!

I had account code as text (I originally had period as text too) because I thought these were hash figures, if they never require to be summed together as such I thought they should always be text - is this bad database practice?

Thanks again!

 

[tehNellie]

it's bad practice if storing "aaaaa" instead of 11111 as an account code would cause you problems. As you've already seen people took one look at your data sample and assumed Account[_code] was a numeric field because it's storing, well, numbers.

If you want some fun, export your table from Access into a text file and then open it in excel or re-import it back into access and see how both of those treat that column by default. I strongly suspect that they'll try and format the column as a numeric value unless you specifically tell it to treat it as text.

If you do import it back into Access, now try and join your two tables together or run any comparison on those two columns and see what happens.

For more fun, order your query by account[_code], as text values you tend to find an ordering along the lines of:

1
11
12
13
14
2
21
22
23
3

This also applies if you run a query using an expression such as Max(), which using the example above would return 3 when you'd perhaps reasonably expect to get 23 because you think you're looking at a number rather than some text.

 

[MoeIndustries]

Thanks for explaining! I will try those excercises - I had already noticed the ordering of text (to my confusion!!)

 

[vbaInet]

Some good points from tehNellie there. Also one thing to note is that searching against a Number field is always faster than doing it on a Text field. Always use the data types that represent the actual data. If there's any formatting you need to do on the field, use the Format property, the Format function or any other function you require.

 

[tehNellie]

I left that bit out, especially around indexing/searching numbers vs text, because I couldn't remember if I'd just made it up But I, funnily enough, agree that in general, set your data type to reflect what you're going to store in it and transform it elsewhere if you really need to.

Dates are another one where people set it to text or store some weirdy variation of a proper date. I have one here that uses a "key" to reflect the reporting month worked out from 1st january 2002 = 1 and so on. Every db application I have that interacts with it now has a little chart that converts the last 12 month reporting dates into this key so people can see at a glance what they need to enter to get the data they need, it's bonkers and causes work for work's sake. /grumble off .

 

[vbaInet]

/grumble off .

- Compile error - grumble on switch missing

 

[MoeIndustries]

Hi again everyone, sorry to keep this up - you've been great so far!

I've just tried the same Dsum expression on a different table with the same set up but when I run the qry everything shows up fine except for the Dsum column which is filled with ERROR - when I click on an ERROR it says "Syntax Error (missing operator) in query expression" but I can't figure out what's missing!!

My formula goes:

DSum("[2012 Bud]","Table","Period <=" & [Period] & " AND Account Number = " & [Account Number])

 

[vbaInet]

Did you really call your table TABLE?

 

[RainLover]

Guys

Please enlighten me.

I had account code as text (I originally had period as text too) because I thought these were hash figures,

In Access what is a Hash Figure.?

 

[MoeIndustries]

Hi again,

Thanks for your continued help!

tehNellie: I made sure everything was number format not text - that's why I now can't figure out what else is wrong.

Did you really call your table TABLE?

No, I simply put that down for this example - would that make a difference?

 

[tehNellie]

If you run that Dsum on it's own from your immediate window in VBA, supplying the relevant values for [period] and [account number] does it run?
ie

debug.print DSum("[2012 Bud]","Table","Period <=" & [Period] & " AND Account Number = " & [Account Number])

 

[RainLover]

Table and various other words are reserved for use by the system. Using them can confuse the compiler and it could create a problem.

When you get the time do a Google on Reserved Words and keep the result for further reference.

While you are at it search Naming Conventions. e.g. Spaces are not normally recommended.

-----------------------------------------------------------------------------------------------------------------

If you can't sort this problem then post a Data Base with just the Table and Query in question.

It might be easier to sort the problem with real Data.

 

[vbaInet]

@RainLover: MoeIndustries explains here

No, I simply put that down for this example - would that make a difference?

@MoeIndustries: AccountNumber might be Null which is why you're getting syntax errors.

 

[MoeIndustries]

This is driving me crazy! If I take out the account part, i.e.:

YTDBud: DSum("[2012 Bud]","Table","Period <=" & [Period])

it works (it seems to total the whole period) - so the issue must be with the account part. When I remove "[Account Number]" and replace it with a number i.e.

YTDBud: DSum("[2012 Bud]","Table","Period <=" & [Period] & " AND Account Number = 6850")

It says "Syntax error (missing operator) in expression"