Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8

    Combine queries results in forms

    Hi, I need some help. Sorry for my english.
    * What I need: get the sum totals of my tables and be able to edit them after in a form for my small bussiness.

    * What I have:
    -Different Tables related for choosing countries, travel agencies, employes
    -All the data entry is trough Forms because I have different employes
    -For every table of the incomings ($) I have 3 different currencies to sum (Euro, Dollars and Shekels)

    TABLES:
    *Incomings

    Agency
    ID
    AgencyID
    Date
    NIS (Israel currency)
    Dollars
    Euro
    Notes

    Singles
    ID
    FirsName
    LastName
    Adress(divided in adress, city, country, etc.)
    NIS
    Dollars
    Euro
    Notes

    Another tables like this ones with more or less the same fields, but all of them with this 3 fields of different currencies.

    *Outgoings

    Library
    ID
    Date


    Employee
    NIS
    Dollars
    Euro
    Notes

    Food
    ID
    Date
    Employee
    NIS
    Dollars
    Euro
    Notes

    Another tables like this ones with more or less the same fields, but all of them with this 3 fields of different currencies.

    So once again, I need to sum all the incomings, all the outgoings, for every month and for every year.
    Am new using Access so maybe am violating all the Access rules making this design, and that's why am not able to do what I need. Thank you for your help.

    Silvio

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109

    Something You Could Try

    Your tables are probably not optimally designed, but I'll assume that you want to keep them this way.

    If you want to sum the euro values, the dollar values, etc. from different tables to view, you could try this...

    Create a union query that joins the table information together. Obviously, one for incoming and another for outgoing.

    ex. Select Date, NIS, Dollars, Euro from tableName UNION Select Date, NIS, Dollars, Euro from secondtableName UNION as so forth...

    I would create a second query off of the union query. Select Totals under the View menu. Create a year and month field from the date field (ex. Year: year([Date]) ). Select group by in the Total row and sort in ascending for both year and month fields. Under your NIS, Dollars, and Euro fields, select Sum in the Total row. This query will be for viewing only.

    I imagine it would have been better to have created a Transaction table with field to identify Incoming/Outgoing, a Date, a currency number, a currency unit (NIS, Dollar, Euro), a category (agency, employee, etc.) field, and identifier fields (agency_ID, employee_Id, etc.)

  3. #3
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8
    Thank you for answering me that soon.

    You wrote that the tables were not optimally disegned, and even if you told me to try that solution I would like to accept any other table design you consider that will be better for me.
    I guess that what I need to do is very simple but I can not do it. I try different ways to design the table but without any luky. Am able to do very good looking forms and reports, and am able to enter the data too without problems, only that at the time of grouping the results am in blank. I had change the table design several times, so I will have no problem to change it once again. Just guide me please. Thanks

  4. #4
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8
    I saw you wrote:

    I imagine it would have been better to have created a Transaction table with field to identify Incoming/Outgoing, a Date, a currency number, a currency unit (NIS, Dollar, Euro), a category (agency, employee, etc.) field, and identifier fields (agency_ID, employee_Id, etc.)

    But I do not understand very well what you are talking about. Do I have to create a table for each one of this things, I mean a table for Incoming/Outgoing, another table for Date, another table for currency number, another table for currency unit, etc? and then I put them all together? how? Maybe is there an access template that I could download?Thank you. I wait your instructions.

  5. #5
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Table design is not exactly my strength. This may take some time.

    Before you create your tables, it's important to understand what you want from the database. What type of information are you trying to get from it and what type of information are you putting into it?

    As an example, are you trying to track incoming income and outgoing expenses? Would you have a single transaction involve more than one currency? Does the incoming come from either an agency or an employee? Does the outgoing relate to an employee? Are there set categories (library, food, etc.) of outgoing? What is the information you are putting into the database?

  6. #6
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8
    Thank you for answering.

    I will try to explain you more clearly, at least as long as my english allow me to do it. I have a small hotel for young turists that started with 10 rooms from some houses I bought a long time ago in Jerusalem. Now, I already have 50 rooms and am planing to expand them to 75 and start changing the way I deal with it. Since now it was fine to me to work with one tabbed excel sheet, but I would like to move one step forward so that I can manage all this information in a more usefull way. I started looking for a solution and I came to know Access, I bought some books and started building my database.

    The things I need from my table are:
    * get by week, month and year the totals incomings and outgoings
    * analize this information in different ways, I mean: graphics to compare one week to another, track the tourists groups activity and so on
    * let my employees work with this database for entering data

    The things I put into it are:

    INCOMINGS
    * Tourists Groups detailed information (Agency, Address, Leader Group, etc) , paying amount (Euro, Dollars or NIS), and a Notes box (I ask them to fill a form so that I know what could be better in my hotel)
    * Single turists with the same items involved
    * Extra meals, single tourists o groups that doesn't sleep in the hotel but eat in it
    * Extra Beverages in the "restaurant" of the hotel
    * Bar incomings from different things like coffee, te, beverages and so on
    * Telephone calls by room and the 3 international telephones that I puted in the Bar and that I manage with another computer
    * Stamps I sell for this tourists, an extra service
    * Tips that they leave for me and my employees
    * Others, where I put any other small thing that not always happends like if someone needed some medicine, a doctor, an ethernet wire, etc.

    OUTGOINGS
    * Salaries for extra employees I take for the day, or not permanently
    * Salaries for the employess
    * Tips, that I divide in equal amounts between the employees
    * Assicurations, for the car, the hotel, life, etc.
    * Medical Assicurations for the employees and myself
    * Fired employees, some times is not possible to have an employee more than 2 or 3 months, different reasons
    * Electricity
    * Telephone and Fax
    * Mobile phones
    * Internet
    * Water
    * Gas
    * Warming system, for the hotel
    * Wash and Clean (Clothes) one inside the hotel, and other outside
    * Tourists Groups of mine in other hotels, sometime I make the accomodations when they go out Jerusalem and I pay this hotels, after I charge this in their account
    * Food for prepairing all the meals
    * Another assicurations
    * Taxes
    * Travels for the employees when they go buy things or if they need to go with any tourist like a guide
    * Maintenance of the hotel


    As you suggest me, am trying to track incoming and outgoing expenses. I have transactions involving more than one currency, some times even more than 5, but 3 are ok for me (NIS, Euro and Dollar). The incomings come some time from an employee in the case of single tourists and some time trough the agency if it is a group. The outgoings are related to some employees, I have one for food, that buys the food, another two for the bar, who buys the beverages, another two for the maintenance, that buys all the needed things like bulbs, chairs, electric things, etc.
    It was very easy to do this in Excel, of course without all the details that I want now, so I guess Access was more or less the same thing. Now I see that it is not. Hope you can help me better with all this specifications. Thanks!

  7. #7
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Let me try to break this down in sections.

    If I understand you correctly, you have employees, travel agencies, and tourirsts. I would have separate table for each one which contains only information that relates to each entity. I would not include any monetary fields (NIS, Dollar, Euro) in any of these tables.

    tblAgency
    Agency_ID (primary key)
    Agency_Name
    Agency_Address
    etc.

    tblEmployee
    Employee_ID (primary key)
    Employee_LastName
    Employee_FirstName
    etc.

    tblTourist
    Tourist_ID (primary key)
    Tourist_LastName
    Tourist_FirstName
    Agency_ID (foreign key - reference to tblAgency)
    etc.

    Does this make sense?

  8. #8
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8

    This is the right path

    Thank you for answering me.

    I tryed to answered you before but Windows decide to install an update and restarted my computer loosing the messagge I was writing you, so am here once again.

    You understand correctly what I need, I have employees, travel agencies and tourists. I already did the tables you suggested my before so am happy to be, finally, in the right path. Plus i did another table, the "country" table:

    tblCountry
    Country_ID (primary key)
    Country_Name
    Country_Flag

    So that when I fill the Table with the "Form" I just choose the Country Name instead of typing it. Considering that we don't speak english as the primary language I could type for ex. "Italy" but an employee could type "Italia" or who nows what.

    So this is already done with a beautifull form and very easy to enter all the data I might need concerning the employees, travel agencies, and tourirsts. Now I need to start filling the database with other tables for the incomings ($) in the different categories and currencyes, and the outgoings ($) in the different categories and currencyes as well. And then I want to be able to track all this data, and be able to manipulate it, make graphics with the results and print it.

    One of the others tables I need for example is (I tryed to disegn it like this):

    *tblBar
    -Receipt_ID (primary key)
    -Data
    -Employee_ID (but show First Name + Last Name in one field)
    -Tourist_ID (but show Group Name, Agency_ID and Agency_Name or only Tourist Name if not related to a group or agency)
    -Items list (number of each item, item: coffee, capuccino, etc., example: 2 coffees $2 - $4, 1 cappucino $4, Total $8)
    -Unit Price and Total
    -Notes box (sometimes the tourists want items served in other ways so I would like to know how to serve them better)

    And like this one another tables for the other incomings that any hotel might have. Like one for the telephone, extra meals and beverages etc. That I can do following the example of the Bar Table.

    Thank you so much

  9. #9
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Arguably, this could be done several different ways, but this is what comes to mind.

    If you have a set of transactions that you use, I would create a separate table that holds the different types of transaction (ex. beverage, meals, telephone, etc.)

    tblIncomingType
    IncomingType_ID
    IncomingType

    To manage your incoming transactions, I would create an Incoming Transactions table. I am assuming that each transaction will only be done in one type of currency. You may want other fields, but they should all relate back to the same transaction.

    tblIncomingTransactions
    IncomingTransactions_ID
    IncomingType_ID
    Tourist_ID
    Employee_ID
    Date
    Quantity
    Charge
    CurrencyType_ID
    Notes

    For outgoing, I would create two similar tables tblOutgoingType and tblOutgoingTransactions. I suspect you can remove the Tourist and Employee fields off of the Outgoing table.

    I would also create a Currency table to store the currency that you accept. This would allow you to track as many currencies as you wish.

    tblCurrencyType
    CurrencyType_ID
    Currencytype (ex. NIS, Dollars, Euro, etc.)

  10. #10
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8

    all the currencies togheter

    Thank you for answering.

    You assumed that each transaction will only be done in one type of currency, but sometimes tourists pay in different currencies, for example the tips, one single tourist can leave the tip in two currencies (NIS and Euro) so I would like to have the possibilitiy of entering 3 currencies at the same time but choosing wich currency am entering, I mean to have 3 choices for payment but with (ex.) 6 tipes of different currencies. Like This:

    NIS EURO DOLLAR
    ------/------/-----------
    5,00 0,00 87,00

    Do not want to be redundant, I would like to have the possibility of choose in this 3 cases wich currency to use and put one as the default currency for each field, just as shown.

    Thanks!

  11. #11
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8

    Add the country flag

    How do I do to add the flag to the

    tblAgency
    Agency_ID (primary key)
    Agency_Name
    Agency_Address
    Agency_Country
    Agency_CountryFlag

    I already put the flag attachment but I do not know how to make it appear next to the country name field. I search the country names with a look up in

    tblCountry
    Country_ID (primary key)
    Country_Name
    Country_Flag

  12. #12
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    I was afraid of this. This kind of goes beyond my expertise.

    With respect to payments, two options come to mind.

    Option one:
    Have a NIS, Dollar, and Euro fields in tblIncomingTransactions instead of the Quantity and CurrencyType_ID. This setup restricts the number of currencies you can use. This is arguably not a good design, although you may decide that it is a better option for you.

    Option two:
    Create another table and remove the Quantity and CurrencyType_ID from tblIncomingTransactions table.

    tblAmount
    Amount_ID
    IncomingTransactions_ID
    Quantity
    CurrencyType_ID

    I would envision having a transaction form based off of the tblIncomingTransactions table or a query using this table. A subform would be added to the transaction form to record the amount. This arrangement would allow you to have multiple payments for a single transaction. This will add a little extra effort in setting up your form. If you have the Northwind database, check out the design of the Orders form.

    I am guessing on the flag part, but based on the tables you are showing...

    I believe you want to have a Country_ID field in your tblAgency table instead of Agency_Country and Agency_CountryFlag. If you have an Agency form, you could add a combo box to the form showing the Countries and using the Country_ID as the link.

    If you want to be able to show the Agency information along with the Country Flag together, I would create a query with those two tables with the Country_ID linked between the two tables and then just select what fields you want to display.

    Hope this helps. Gotta run for now.

  13. #13
    Join Date
    Jan 2008
    Location
    Jerusalem
    Posts
    8

    Good!

    Thank you for answering.

    I choosed the first option:

    Option one:
    Have a NIS, Dollar, and Euro fields in tblIncomingTransactions instead of the Quantity and CurrencyType_ID. This setup restricts the number of currencies you can use. This is arguably not a good design, although you may decide that it is a better option for you.

    because it sounded better to me and because my tables were originaly designed in that way.

    For the "flag" maybe it was not a good idea so am not thinking about it right now, anyhow if you could tell me how to add it to my table or form it will be nice.

    Now I did all the tables and started with the forms entering all the data in them. Now I have some questions about them:

    * I did the form based on the tblIncomingTransactions, now I have the possibility to choose between the different Transactions Tipes to fill this Form. But not allways I need all the field to be filled on the form. For example, if I want to add a BAR transaction I fill it with the date, employee, tourist ID and receipt number. If I want to add a TIP transaction I might not need the receipt number field, and I do not want that me or any employee by mistake fills this field. So how do I do to activate or not some fields for being filled or not?

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

Similar Threads

  1. combine three tables in a query
    By neuenglander in forum Queries
    Replies: 0
    Last Post: 08-21-2008, 04:02 AM
  2. Restricting Results In Reports
    By Hawkx1 in forum Reports
    Replies: 0
    Last Post: 06-24-2008, 09:53 AM
  3. Newbie Needs help with combine/average
    By wfd1753 in forum Queries
    Replies: 2
    Last Post: 05-16-2008, 06:43 AM
  4. form formula to combine two fields
    By InvGrp in forum Forms
    Replies: 1
    Last Post: 10-20-2006, 12:10 PM
  5. Get the sum of decimal values - weird results
    By BengtCarlsson in forum Queries
    Replies: 2
    Last Post: 02-10-2006, 04:29 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