Results 1 to 3 of 3
  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77

    Trouble creating a query with all the information I need

    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.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need left joins from TblTransactions to the other two queries, showing ALL transaction numbers from TblTransactions and any values that may or may not exist in the other 2. Right-click on the join and change the type.

  3. #3
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Thank you very much. I right-clicked, chose to show all from TblTransactions, and it worked a treat!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble creating this graph
    By tlozoot in forum Forms
    Replies: 1
    Last Post: 05-03-2014, 12:44 AM
  2. Trouble With Information That keeps repeating
    By RoundTrip in forum Access
    Replies: 4
    Last Post: 04-05-2014, 10:03 AM
  3. Replies: 1
    Last Post: 05-23-2013, 10:00 AM
  4. Creating a Relationship? (Having trouble)
    By lpmndcte in forum Access
    Replies: 3
    Last Post: 05-17-2012, 06:53 AM
  5. Replies: 1
    Last Post: 09-08-2011, 06:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums