strive4peace
AWF VIP
- Local time
- Today, 10:52
- Joined
- Apr 3, 2020
- Messages
- 1,055
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.
""
for Null.= Nz( [fieldname],0 )
= CCUR( Sum( [fieldname], 0 ))
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?
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.
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'd be using a query grouping by PID and EngagementsID, Sum(Amount) ?