Table design flaw?

[MikeLeBen]

I decided there's a flaw in my table design after several days of inability in solving a problem with the "residue" field in one of my tables.

I have the following situation:

- tblCustomers
pkCustID
[other fields..]

- tblOrders
pkOrderID
fkCustID
[other fields..]

- tblCustPrescriptions
pkCustPresID
fkCustID
PresWorth[Currency]
PresResidue[Currency]

- tblPayments
pkPaymentID
fkCustID
fkOrderID
fkCustPresID
Amount[Currency]
Due[Currency]

Some customers have prescriptions which come - for those eligible -, each month, in various worths. They give these prescriptions to the shop and use them over time to buy what they need.
Hence the need of a "Residue" field: when customers return we are immediately able to tell them how much they have left on their prescriptions.
So far the residue has been calculated with spreadsheets, but I'm trying to implement this feature in the access database I'm working on, but I'm at an impasse and have failed so far.
Is it because of a flawed design?

 

[jzwp22]

If the residue amount is determined by this: InitialWorth-(sum(orderamounts)) then you do not need to store the residue amount but rather calculate it on the fly when you need it.

 

[MikeLeBen]

The answer was so simple, yet so elusive to me. Thanks for the enlightening as usual, my mentor

I had a listbox that used the (now gone) residue field, among others, as row source. I'm given to understand once the rowsourcetype property of the listbox is set to "table/query", you can't have a column display, for instance, a variable from the vba code.
Would you suggest I give up on the listbox and use some other control to show the calculated value or is there a way I can have it shown in it?

 

[jzwp22]

I had a listbox that used the (now gone) residue field, among others, as row source. I'm given to understand once the rowsourcetype property of the listbox is set to "table/query", you can't have a column display, for instance, a variable from the vba code.

I'm not sure I understand what you are saying above. A list box can get its data from a table/query or you can supply it directly (value list). VBA would only be able to reassign the row source to either another table/query or another list (I've never supplied a value list to a list box via VBA, so I assume that it could be done).

Would you suggest I give up on the listbox and use some other control to show the calculated value or is there a way I can have it shown in it?

Of course, you can have the calculated field as part of a query and use that for the list box's row source.

 

[MikeLeBen]

I was wondering if a thing such as a "hybrid source" listbox might exist, but I reckon it does not.

I wanted to use vba for calculations as I'm slightly more familiar with it than SQL.
The simple textbox showing the residue I have obtained with

MyAvailable = Nz(DSum("ValoreRicetta", "Clienti_Ricette", _
                      "IDCliente= " & cboRicetteEsistenti.Column(0)), 0) - _
              Nz(DSum("Importo", "Pagamenti", _
                      "IDCliente= " & cboRicetteEsistenti.Column(0)), 0)

Me.txtAvail = "€ " & MyAvailable

whereas the .rowsource for the listbox is the following (now populates 4 columns instead of 5 - where the field residue used to be):

.RowSource = "SELECT IDRicetta, MeseRicetta + '-' + AnnoRicetta, ValoreRicetta, " & _
                    "DataInserimento " & _
                    "FROM Clienti_Ricette " & _
                    "WHERE [IDCliente]= " & Me.cboRicetteEsistenti.Column(0) & _
                    " ORDER BY DataInserimento DESC;"

I deem a much more complex query would be necessary to implement the calculations I need

 

[jzwp22]

I deem a much more complex query would be necessary to implement the calculations I need

A little more difficult, you can use the DSum() in the query to get the sum of the earlier transactions or you can use a subquery that does the same. Once you have that sum value you can subtract it from the initial amount to get your residue.

 

[jzwp22]

I would probably approach it like the following. (Not tested and not sure of your table structure)

query name: qrySumValoreRicetta
SELECT IDCliente, Sum(ValoreRicetta) as SumOfValoreRicetta
FROM Clienti_Ricette
GROUP BY IDCliente


query name: qrySumImporto
SELECT IDCliente, Sum(Importo) as SumOfImporto
FROM Pagamenti
GROUP BY IDCLiente

query name: qryResidue
(I assume that you will have 1 SumOfValoreRicetta for each client; if not, you will have to do a unequal join from the client table first)

SELECT IDCliente, nz(SumOfValoreRicetta,0)-nz(SumOfImporto,0) as Residue
FROM qrySumValoreRicetta LEFT JOIN qrySumImporto ON qrySumValoreRicetta.IDCliente=qrySumImporto.IDCliente


Now join the above query to your list box row source query

SELECT IDRicetta, MeseRicetta + '-' + AnnoRicetta, ValoreRicetta, DataInserimento, (SELECT qryResidue.Residue FROM qryResidue WHERE qryResidue.IDCliente=Clienti_Ricette.IDCliente) as ResidueAmt
FROM Clienti_Ricette
WHERE [IDCliente]= comboboxreference from form
ORDER BY DataInserimento DESC;"

 

[MikeLeBen]

As always, thank you for your time jzpw22 - your help is always invaluable and when I address you as mentor I do mean it (I barely knew how to open access a couple months ago).

The solution you provided basically does the DSum I used to run in VBA with SQL, but I have explained what I'm trying to do more thoroughly and understandably in this thread.
If you have the time to take a look at it let me know if you think it can be done.