Help with accounting tables (1 Viewer)

tmyers

Well-known member
Local time
Today, 17:05
Joined
Sep 8, 2020
Messages
1,090
This is going to be a little in depth (and possibly in the wrong sub-forum). This involves Power BI but I think I can solve it within Access.

Here is some info:
I have my primary facts table (tblTicketDetails) and a dimension table to filter it by (tblCustomers) with a table in between (I forget the term for it) to handle the relationship (this snip is within Power BI, but the tables reside in Access). tblCustomers is related to LookupAccount via [Account #] with a 1 to many and LookupAccount is related to tblTicketDetails via [LookupAccount] and [Account #] via a many to many
1655811110840.png

Here is the relationship between LookupAccount and tblTicketDetails for a little more info.

1655811351043.png


The problem:
We have some accounts that could be considered "pass-through", which in other words is when a sale is made to one customer but goes through another. A example would be that within tblTicketDetails, there is a sale 123456 sold to customer 1 but is technically for customer 2. In our sales system, this sale will only ever show up for customer 1 when I need it to also show for customer 2. The method I have used to tie the two together, is via LookupAccount within tblCustomers. For nearly 2500 accounts, both [Account #] and [LookupAccount] are the same value, but for instances where I need the sale to go towards two customers, the values are different (there are roughly 50 accounts that need this). The primary [Account #] shown will be that of the customer that our system shows it was sold to but the [LookupAccount] will be the account of the second customer that the sale also needs to show for.

Here is a small list of examples:
1655811907083.png

In the above picture, all the values shown in [Account #] belong to customer 1, with the values in [LookupAccount] belonging to others. In terms of Access, viewing all accounts for customer 1 would be simple, as those are their accounts, but say you wanted to view all of customer 2's accounts, who for this example lets say their only account is 5809, how you also bring in customer 1's accounts 23634, 37765, 67804 and 95511?

Logically for the query, I think of it as customer 2 has accounts 1-4, so I want to see all records where [Account #] is 1-4 but also where [LookupAccount] is also 1-4.

I have been trying for days to figure this out with not much luck, as this appears (to me anyways) to be a complicated many to many relationship between tblCustomers and tblTicketDetails.

Any help would be greatly appreciated. This is also related to https://www.access-programmers.co.uk/forums/threads/issue-with-query.323684/ as the query in that post was one method I had been trying to use to link things together.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:05
Joined
May 7, 2009
Messages
19,169
can you Join the LookupAccount field to "another copy" of tblCustomers?
 

tmyers

Well-known member
Local time
Today, 17:05
Joined
Sep 8, 2020
Messages
1,090
I have tried doing something very similar to that and it still did not yield the correct result.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:05
Joined
Feb 19, 2002
Messages
42,976
Can you post the Access view of the relationship with all columns showing. I don't understand the PBI view.
 

Cotswold

Active member
Local time
Today, 21:05
Joined
Dec 31, 2020
Messages
521
Maybe I'm wrong but this seems like customers with depots, or branches.
In those cases each record in the customer table has a Customer Code and an Account Code. You then create the sales tickets to the customer, aligned to the Customer Code. Tickets or delivery notes are printed/emailed to the address for the Customer Code.
Then at month end you form invoices by Account Code + Customer Code. You can decide how invoices are formed, all depots on one invoice, or probably a separate invoice for each depot, which are sent to the address/email for the Customer Account Code. In your accounts system you will import invoices by Account Code and not Customer Code.
You can of course create sales tickets to the Account Code by having the Customer Code and Account Code the same on the record. This may well be the case for the majority of customers. The transfer/import invoices to accounts by Account Code will ensure the statements for payment will be correct.
I really do not see the need for the complications of lookups

A tip I will give is that you MUST store the results of each charge line as integers in your ticket table. Do the goods calcs with any discounts
and convert that to an integer/long on the ticket. At the same time calculate the VAT/TAX and store that on the ticket.
(you should calc tax on each charge line anyway and not on the invoice total, because the VAT rate can be different product by product)
Then when you create invoices you simply add those fields and string handle the decimal point correctly for the goods and tax. If you use a single, double or currency you may find that the printed line totals do not add up to the invoice total, particularly with large invoices. If you create an invoice and the lines totals are one penny out to the invoice total, that invoice is unacceptable. Regardless of if the invoice is for £50 or £50,000.
It usually a problem with discounts or VAT rates. The VAT is less of an issue at 20% but will be a problem at 17.5% or similar. Singles, doubles and currency actually drag maybe 16+ decimal places around, despite the mask you choose. Adding a large number of these will give rounding errors unless you use integer calcs.

May as well get it right at the start because changing it later will be a nightmare.
 
Last edited:

Users who are viewing this thread

Top Bottom