I Work for a governmental institution where we track certain types of goods across borders and so we need a database with information about certain Companies and their transactions.
I have created a table of 'TblTransactions', with a column of 'transactions number'.
To each transaction (identified by the transaction number) there may be a number of different countries as interested parties. I have therefore also created a table of 'TblCountries' and a table of 'TblInterestedcountries'. The first column of 'TblInterestedcountries' is a bulletin of the transaction numbers from the table 'TblTransactions' and the second row of the table 'TblInterestedcountries' is a bulletin of the countries in the table 'TblCountries'.
To each transaction there may also be a number of transports. I have therefore created a table of 'TblTransports' and the first column of that table is a bulletin of the transaction numbers from the table 'TblTransactions'.
I get this:
-In the table 'TblTransactions' I can store data of each transaction by transaction number (there is also additional information about each transaction in the table 'TblTransactions' but they are irrelevant here).
-In the table 'TblInterestedcountries' I can store data of any number of countries that are interested parties to each transaction.
-In the table 'TblTransports' I can store data of any number of transports related to each transaction.
Now I wish to create a Query where I can see all the columns from the table 'TblTransactions', and where I can also see the amount of interested countries that are related to each transaction number (not necessarily which countries specifically), and where I can also see the amount of transports related to each transaction number.
I therefore create a totalling Query that tells me the number of interested countries grouped by transaction number. I call this 'QryAmountOfCountries'. This results in a Query where each row identifies the number of interested countries for one transaction number each.
I also create a totalling Query that tells me the number of transports grouped by transaction number. I call this 'QryAmountOfTransports'. Likewise, I get a Query where each row identifies the number of transports related to one transaction number each.
No transaction number is seen twice in the same Query.
Finally, I create a Query where I ask to see 'TblTransactions', I ask to see 'QryAmountOfCountries' andf I ask to see 'QryAmountOfTransports'.
I relate the transaction number from 'TblTransactions' to the transaction number in 'QryAmountOfCountries' and to the transaction number in 'QryAmountOfTransports'.
This results in a Query where each row shows one transaction number, the additional information from TblTransactions, and shows the information about the number of interested countries and the number of transports related to each transaction number. Again, no transaction number is seen twice in the same Query.
I call this Query 'QryTotal'.
Here's the problem:
QryAmountOfCountries only shows me a fraction of the transaction numbers, namely those where the transaction number happen to appear in a row in TblInterestedCountries. If no interested country is assigned next to a transaction number in TblInterestedCountries, that transaction number does not show up in QryAmountOfCountries.
Likewise, if a transaction number does not appear in TblTransports, it also does not appear in QryAmountOfTransports.
The problem carries over into QryTotal. My end result is I cannot see those transactions in QryTotal unless at least one transport and one interested country is assigned to their respective tables beside the transaction number.
I would have expected that QryAmountOfCountries and QryAmountOfTransports would have shown the transaction number and a total of 0 interested countries and 0 transports.
I can create a workaround this way: If every time I assign a new transaction number to TblTransactions, I also assign that number to a row in TblInterestedcountries and assign the same number Again to a row in TblTransports, then these transaction numbers show up in my queries with a total of 0 countries and 0 transports. The workaround is unsatisfactory since this demands the user to remember to assign the transaction number to three tables instead of one table, every time a new transaction is created.
What would you suggest? Can I perhaps create a macro that enters the newly inserted transaction number from TblTransactions into TblInterestedCountries and TblTransports each time a new transaction number is assigned to TblTransactions? Other possibilities.