I need some MS Access help

[iamgenius]

Okay. It has been years since I last used access. I think it was 2004, so that's what? 9 years! So, it is safe to say that I forgot all about tables, quries, and forms. Anyways, I'm in need for it again, but--time wise-- I don't have the luxury of reading a whole guide on it, so I started this thread to help me jump start and refresh my memory.

What I need is simple I guess. I'll try my best to explain it here. We are working on a project and we of course have expenses. The Logistics guy asked me to build a simple database program to help him manage his bills. Yes, it is all about bills & receipts. We spend money on so many different categories of course ( Car rentals, fuel, food, electrical equipment....etc). There is a receipt for each purchase.

The basic building block of access database is tables if I remember correctly. So, I'll have to build a table for each category. The table will have-for example-an amount field, material field, date and the primary key field(auto generated).

The guy has all his records in excel files now, and here is what he wants to be able to do:

If asked how much did we spend on food from May 5th till July 2nd, he will be able to just pull it. If asked how which month was the most expensive (the month in which we spent the highest amount), he will also be able to just pull it of the data base. If asked what was the biggest bill before April, he will also be able to do it......etc.

Am I making myself clear here?

I'm still confused about many things, which I will talk about below:

Tables will each need a primary key, right? And about relationships, each table will have to have a relationship defined with every other table?!? I'm confused here when it comes to relationships.

And about queries, what are they really? special tables? or tables with specific values derived from the original ones?

And If I remember correctly, forms are used to show and also input data. right? So, if your database is growing, you will use then forms to enter the new data, right?

Finally, there are the reports which are used to show part or specific type of data in a printable format.

I know what I need is all in what I mentioned above, so please help me. From my memory, I don't think what this guys needs is a complex database, right?

If you can help me here, please do so. If you see that I'm in the wrong direction and this guy request requires more serious work, then tell me go buy a guide and help yourself.

Thanks.

 

[Dulanic]

Tables hold data. With a small database like yours just let access add it's own keys.

Relationships basically join the data where applicable. For example, you could have a table that stores each receipt and one that stores each vendor and. So when you are adding a receipt you already have all the vendor info from the vendor table. And that can be done via queries which is a way to display and our join tables. You just need something to join those tables together. That can be a number you assign each vendor or just their name.

With access there is a dozen ways to get to the same result differently. For your goal, I doubt it will make a huge difference how you design it.

 

[EagleKeeper]

Table for each category (column) in the spread sheet.
Fields should be category index, sub-category index, description index, amount, date, notes, user index(?)

Having the indexes makes it easier to extract data and also consistency of the data - re-use the text fields.

then just generate queries for what every reports are needed

 

[iamgenius]

Table for each category (column) in the spread sheet.
Fields should be category index, sub-category index, description index, amount, date, notes, user index(?)

Having the indexes makes it easier to extract data and also consistency of the data - re-use the text fields.

then just generate queries for what every reports are needed

Can you please tell me what exactly an index is here? I want you to expand here please.

What this guy has is many tables( about 25) where one table serves one category. Each table has the these standard columns: number, category, amount, date, and notes. And then there is an extra table for calculating the total for each category and the grand total.

 

[iamgenius]

Hey guys, please don't leave me alone on this! Just little more help, and I think I'll be to do it. In my case and from my explanation, will there need to be a relationship between tables?

And regarding the total table, does access do computations like in excel? I mean: if asked how much did we spend in total for the month of May for example, can this be computed somehow OR it will just have to be pulled from a table?

 

[iamgenius]

Anybody please?

 

[Dulanic]

Hey guys, please don't leave me alone on this! Just little more help, and I think I'll be to do it. In my case and from my explanation, will there need to be a relationship between tables?

And regarding the total table, does access do computations like in excel? I mean: if asked how much did we spend in total for the month of May for example, can this be computed somehow OR it will just have to be pulled from a table?

Relationships are ways to have data housed separately while avoiding redundancy. For example... let's say you have customers so they have a table, then you take orders thats another table then you have the items that can be ordered thats another table. You want to split it because if you housed it all on the same table you'd have to store the customer information every single time they placed an order.

With relationships say you have a customer ID. So on the order table you have the order info and just the customer ID which is joined to the customer table. That way you don't store the customer information a 2nd time.

OK so on to calculations. Short answer yes, it's done with a queries. For your example you set the query to sum total with a criteria of the date field being in may.

BTW, you may want to read up on relationships. http://office.microsoft.com/en-us/access-help/guide-to-table-relationships-HA010120534.aspx

 

[EagleKeeper]

Can you please tell me what exactly an index is here? I want you to expand here please.

What this guy has is many tables( about 25) where one table serves one category. Each table has the these standard columns: number, category, amount, date, and notes. And then there is an extra table for calculating the total for each category and the grand total.

did not mean to leave you hanging.

As an example
IamGenius, iamgenius, IAmGenius, IAMGENUIS

All the above point to the same person, as you eyeball it.
However, when doing character comparisons using a computer, they are up to 4 different items. then you bring in spaces and it becomes even more difficult; The computer sees in absolutes.

So what one has to do is define an entry in a table (Names) with the data iamgenius.
Assuming that there are 5 previous entries; the index for iamgenius will be #6.
When one wants to enter data for iamgenius, they select from a previously defined table instead of entering the person directly.

Also, the use of an index is faster to retrieve that data vs having to do a character comparison search.


Now for your issue, each table in this spreadsheet should have an entry that was free formed. One time it may say fuel, another may say gas, petrol, shell,speedway, etc.

How does he find out how many times he got gas at Shell to reconcile his billing on his Shell card.
Query the table that for entries of Shell.
But what if there were 5 on the statement and the query only pulls up 4. (1 was listed as fuel).

such is the benefit of standardization/normalization. (big words but all they mean is getting the data into a format/relationship that can be most efficient).

Once you have the underlying support tables; the final thing is to create an entry table and select from those support tables, the item needed.

PM if additional info required

 

[iamgenius]

Relationships are ways to have data housed separately while avoiding redundancy. For example... let's say you have customers so they have a table, then you take orders thats another table then you have the items that can be ordered thats another table. You want to split it because if you housed it all on the same table you'd have to store the customer information every single time they placed an order.

With relationships say you have a customer ID. So on the order table you have the order info and just the customer ID which is joined to the customer table. That way you don't store the customer information a 2nd time.

OK so on to calculations. Short answer yes, it's done with a queries. For your example you set the query to sum total with a criteria of the date field being in may.

BTW, you may want to read up on relationships. http://office.microsoft.com/en-us/access-help/guide-to-table-relationships-HA010120534.aspx

Okay thanks. But is this what you meant by being able to do calculations?



?

How do I set that field so that it shows the sum of what's in them amount field? Or Am I approaching this incorrectly?

 

[iamgenius]

did not mean to leave you hanging.

As an example
IamGenius, iamgenius, IAmGenius, IAMGENUIS

All the above point to the same person, as you eyeball it.
However, when doing character comparisons using a computer, they are up to 4 different items. then you bring in spaces and it becomes even more difficult; The computer sees in absolutes.

So what one has to do is define an entry in a table (Names) with the data iamgenius.
Assuming that there are 5 previous entries; the index for iamgenius will be #6.
When one wants to enter data for iamgenius, they select from a previously defined table instead of entering the person directly.

Also, the use of an index is faster to retrieve that data vs having to do a character comparison search.


Now for your issue, each table in this spreadsheet should have an entry that was free formed. One time it may say fuel, another may say gas, petrol, shell,speedway, etc.

How does he find out how many times he got gas at Shell to reconcile his billing on his Shell card.
Query the table that for entries of Shell.
But what if there were 5 on the statement and the query only pulls up 4. (1 was listed as fuel).

such is the benefit of standardization/normalization. (big words but all they mean is getting the data into a format/relationship that can be most efficient).

Once you have the underlying support tables; the final thing is to create an entry table and select from those support tables, the item needed.

PM if additional info required

Thanks for your nice reply. However, I'm still little confused. I'll just show you a sample of the sheet so that it is easier for me and you.

Here:




I understood what you explained about indexes, but I don't see how it is done in tables. I don't see how it relates to your previous sentence "Fields should be category index, sub-category index, description index, amount, date, notes, user index(?)"

An index is just a number within a series of numbers.

How can there be more than one index in one table?

 

[EagleKeeper]

Each table can have a primary index.
A field can also be an index to another table.

In the example; you have the Type Hotel. What happens if instead of HOTEL, they used Sheraton as an entry.

I would expect that there is another grouping for Auto Rentals, Airlines, Food, Fuel.

So to type and find what happened on 6/19 one has to look into all those support tables and create a report

Or you have one table that has all the entries for 6/19 and indexes to categorize them as to why. The report search will pull in the explanations based on the support indexes.

this way; one knows that the airline was an UNITED with a HERTZ rental and a SHERATON and shows as a single item with the total expense calculated.

You can also then drill down into the report to see details of each category. Was the Sheraton a Queen, King or Suite. Was United coach or business class.

Such info is available; depending on how granular the data tables are.

 

[iamgenius]

Each table can have a primary index.
A field can also be an index to another table.

In the example; you have the Type Hotel. What happens if instead of HOTEL, they used Sheraton as an entry.

I would expect that there is another grouping for Auto Rentals, Airlines, Food, Fuel.

So to type and find what happened on 6/19 one has to look into all those support tables and create a report

Or you have one table that has all the entries for 6/19 and indexes to categorize them as to why. The report search will pull in the explanations based on the support indexes.

this way; one knows that the airline was an UNITED with a HERTZ rental and a SHERATON and shows as a single item with the total expense calculated.

You can also then drill down into the report to see details of each category. Was the Sheraton a Queen, King or Suite. Was United coach or business class.

Such info is available; depending on how granular the data tables are.

Assuming that all tables are just like the one I posted, but only with different type field. How would you do it? There will be no more details in each table.

 

[Dulanic]

Okay thanks. But is this what you meant by being able to do calculations?



?

How do I set that field so that it shows the sum of what's in them amount field? Or Am I approaching this incorrectly?

You're thinking about it wrong. Calculations on a table are static calculations that happen once when the record is added. For example you can add a month field that calculated the month of another field that you enter (useless, just an example). A query is dynamic and recalculates every time it is run.

Ex of calculated field:

How that would look:

Building the query to show the sum by person:

End result of how that query would show the result:

Does that make a bit more sense?

 

[EagleKeeper]

Left hand field can be considered to be an index.

Now let assume that you have a table for Hotels Names
That has 8 entries with indexes of 1-8

change where in the first table you have Hotel as a text string to an numeric value that corresponds to which type of hotel in the Hotel Name Table.

Now you have a primary key for the travel and a secondary key to allow you to locate what hotels have been used and get an idea of the cost of the each hotel.

 

[iamgenius]

Okay guys, thanks for all the help. I guess I'll start and things will be more clear when I actually do them. I'll come back if there are specific questions.

Thanks again.