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

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.)

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

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.

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?

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!

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?

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

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.)

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!

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

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.

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?