VBA help

[strive4peace]

wonderful, Neil! that's awesome!

 

[neilsolaris]

My database is coming along well now, thanks for your help so far.

I've got a quick question before I enter too much data. I have multiple currency fields in the EngagementsPlayers table. I initially left blank records blank, which I believe are referred to as null? This caused problems when I tried to execute formulas in the query, which the Nz function didn't solve perfectly (no doubt I was doing something wrong). Also, I couldn't work out how to set the formula fields to currency, and the totals at the bottom had way too many decimal places! So I changed all the blank cells in the table to zero, and the problem has been solved. However, I find blank cells cleaner and easier to read.

Does anyone have any advice about whether I should go back to having null values, or should I stick to.zero values?

Many thanks.

 

[strive4peace]

My database is coming along well now, thanks for your help so far.

I've got a quick question before I enter too much data. I have multiple currency fields in the EngagementsPlayers table. I initially left blank records blank, which I believe are referred to as null? This caused problems when I tried to execute formulas in the query, which the Nz function didn't solve perfectly (no doubt I was doing something wrong). Also, I couldn't work out how to set the formula fields to currency, and the totals at the bottom had way too many decimal places! So I changed all the blank cells in the table to zero, and the problem has been solved. However, I find blank cells cleaner and easier to read.

Does anyone have any advice about whether I should go back to having null values, or should I stick to.zero values?

Many thanks.

hi Neil,

wonderful! you're welcome ~

Null is the ABSENCE of a value and has significance in that you can tell that nothing yet has been entered. I agree that "blank cells [are] cleaner and easier to read" -- much easier to spot values that need filling. If ALL zeros need a real value, you could use # in the pattern for the Format property so a zero doesn't show.

When using NZ, it is best to supply the optional second argument to be 0 (zero) if the value should be a number. Otherwise, if data us in a TEXTBOX that isn't bound, Access will guess it is text and use "" for Null.

=  Nz( [fieldname],0 )

Calculated controls can (and should) have their Format property set -- that will eliminate the extra decimal places being displayed However, perhaps you also need to round the result or convert to currency in the formula? What is the data type of the column you're summing? You probably want to match that.

> "set the formula fields to currency"

convert to Currency is CCur. You could do something like this in the Control Source:

= CCUR( Sum( [fieldname], 0 ))

that will get the accuracy of the currency data type (only 4 decimal places) -- you might also need to set the Format property of the control for it to display as you like.

 

[neilsolaris]

Hi Crystal,

Many thanks again. I've changed the zero cells back to null now, as I'm convinced that that'll be better in the long run. I'll try what you suggested, about entering the second argument in the Nz function, as I think that might well have been the reason it wasn't working before.

Before I carry on though, I wonder if there's any further normalization I need to do, in relation to the numerous currency fields. I have 12 such fields, to cover categories such as fee, porterage, doubling, travel, early start, late finish, overnight, costume fitting, unsociable hours etc. For most jobs, the majority of these fields will be blank. Is there a way of creating another table, in order to save space? I can't quite think how it's possible though.

The currency I'm using for all these fields is the British pound, which I'd like to set to two decimal places. I'll try what you suggested though, and see if I can make it work.

I noticed that I might have another potential (minor) problem. Before I took over the bookkeeping of this orchestra, a small handful of players were accidentally paid VAT when they weren't VAT registered, some were not paid VAT when they were registered (they were subsequently paid their VAT afterwards), and some who were VAT registered before have now de-registered. When entering the old data, it's entering the VAT correctly based on who is currently registered. Is there any way I can change individual records in a formula field, to reflect what actually happened? I hope I explained that clearly!

Thanks again. I'll let you know what happens!

 

[strive4peace]

hi Neil,

you're welcome

instead of a different field for each type of fee or expense, it would be better to have a table to define CostTypes and then a related table with the actual amounts

Costs
CostID, Autonumber, PK (primary key)
CID, number, long, default value = 0, FK (Foreign Key) to contacts -- or whoever to attribute this cost to
CostTypeID, number, long, default value = 0, FK (Foreign Key) to CostTypes
EventID, number, long, default value = 0, FK (Foreign Key) to Events
dtCost, date/time, date that cost incurred
CostAmt, currency, amount of cost
NoteCost, short text, 50, note about this cost

then you can just create records as needed, and they'll be lots easier to add up!

Maybe one CostType will be for adjustments. If the amount is negative, then it is a credit -- or perhaps you want all costs to be negative and credits to be positive

 

[neilsolaris]

Thanks Crystal! I'll take a moment to get my head around it, then see if I can make it work.

 

[strive4peace]

thanks, Neil and you're welcome!

one of the biggest obstacles is seeing your data from a different perspective. Once you can simplify the data structures to be as they really are, the rest falls into place ~

 

[neilsolaris]

I've just finished implementing your suggestion in terms of creating two new tables, PaymentType and Payments. It all works very well. However, when I run a query, there is one column for payment type, and another column for the amount. Ideally I'd like a separate column for each payment type, so that I can add the values along the row to reach a total for each player. Is that easily achievable?

 

[strive4peace]

hi Neil,

> "finished implementing your suggestion in terms of creating two new tables "

wonderful! Although I didn't specify details for the CostTypes table, did you set the Size for the CostTypes field to be something other than 255? .... obviously, that is way too long! ... and possibly make it a Unique index too?

> " I'd like a separate column for each payment type, so that I can add the values along the row ..."

actually not! ... think about it ... do you really want to add another field each time you realize there is another cost type? No.

Use a related table instead of more fields in the current table -- break it down!
You can then have none, or many, related records.

with all related values in another table, you can simply Sum amounts! ... so, instead of a formula with fieldnames (some of which are Null, adding to further complications), you can simply sum amounts with the same reference Use Access the way it is designed to be! Let Access do your heavy lifting ...

... for instance, with the DSum domain aggregate function, or the Sum aggregate function

Costs
CostID, Autonumber, PK (primary key)
CID, number, long, default value = 0, FK (Foreign Key) to contacts -- or whoever to attribute this cost to
CostTypeID, number, long, default value = 0, FK (Foreign Key) to CostTypes
EventID, number, long, default value = 0, FK (Foreign Key) to Events
dtCost, date/time, date that cost incurred
CostAmt, currency, amount of cost
NoteCost, short text, 50, note about this cost

 

[neilsolaris]

Thanks Crystal, I'm so glad you're here to help me!

I did think to reduce the PaymentTypes, I set it as 20 characters maximum.

I'm glad to hear I'm on the right track. So with the various payment amounts, are you suggesting, for instance, that I create one table specifically for the fees, another table for Porterage, another for travel etc? Or just one separate table that separates out the various categories?

 

[strive4peace]

Thanks Crystal, I'm so glad you're here to help me!
<clip> So with the various payment amounts, are you suggesting, for instance, that I create one table specifically for the fees, another table for Porterage, another for travel etc? Or just one separate table that separates out the various categories?

you're welcome!

aaah, the lightbulb is on ... you see ... "just one separate table that separates out the various categories?" -- yes!

 

[neilsolaris]

Great, thanks!

 

[neilsolaris]

I'm in the process of creating the extra summary table, but I'm a little stuck. I want to be able to have information for each player for each job and for each category. For instance, for job 2, I'd like to select a player, and see what fee and other payments if any they received for that particular job. What fields would I need to achieve this?

Many thanks.

 

[strive4peace]

I'm in the process of creating the extra summary table, but I'm a little stuck. I want to be able to have information for each player for each job and for each category. For instance, for job 2, I'd like to select a player, and see what fee and other payments if any they received for that particular job. What fields would I need to achieve this?

Many thanks.

you're welcome

In the table with the cost type and amount, include foreign keys for all the ways you want to attribute it -- unless one of your foreign keys leads to others -- those won't need to be duplicated.

 

[neilsolaris]

Many thanks Crystal.

 

[neilsolaris]

T_Payments

ID	PID	PaymentTypeID	EngagementsID	DtPayment	Amount
94​	629​	Fee​	0004​	03/10/2019​	£626.40​
95​	629​	Porterage​	0004​	03/10/2019​	£40.30​
96​	629​	Overtime​	0004​	03/10/2019​	£380.25​
97​	629​	Travel​	0004​	03/10/2019​	£80.50​
98​	629​	Costume Fitting​	0004​	03/10/2019​	£94.90​

I'm sure it's me being stupid, but I still haven't been able to solve my dilemma! I've attached part of the table in case I didn't explain very well before what I was trying to achieve. Basically, in the above example, there are five records relating to the same person (629), and relating to the same job (0004). I need to display the net totals for each person for every job, in a table or query. In this particular case the net total is £1,222.35.

I could probably achieve this with VBA, but I'm hoping for a less fiddly solution if possible. Could the DSUM function be made to work? I did try it but without success.

If anyone could talk me through a solution I'd be very grateful. Many thanks for your help.

 

[Gasman]

I'd be using a query grouping by PID and EngagementsID, Sum(Amount) ?

 

[neilsolaris]

I'd be using a query grouping by PID and EngagementsID, Sum(Amount) ?

Thanks Gasman. That sounds really straightforward. I'm not entirely sure what grouping means in this context though. Do I simply create PID and EngagementsID columns in a query? Or is there more to it than that?

 

[Gasman]

In the query builder, pull in PID, EngagementsID and Amount. Then press the Totals button.
A new row will appear called Total each field will have Group By. Change the Amount field to Sum, and run the query.

 

[neilsolaris]

Thanks very much Gasman, that worked like a dream! You wouldn't have guessed how long I've spent trying to solve this, and I would never have worked this out by myself.