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
Here is the relationship between LookupAccount and tblTicketDetails for a little more info.
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:
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.
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
Here is the relationship between LookupAccount and tblTicketDetails for a little more info.
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:
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.