using excel as a relational database

[eilerc]

I'm building a length of stay study for a county jail and would like to do the entire thing in excel.

I have gathered data regarding in date, out date, time of booking in time of booking out and charges. I need to be able to display data like:

number of assault and battery charges on a given day and how many of these charges stayed in jail 5 - 10 days, 10 - 20 days, etc.

How could I set this up so people could choose the offense and time and get the correct data?

Thanks

[Dave Hawley]

Hi


I would investigate a Pivot Table option, sounds well suited from what you describe.

[eilerc]

Yes - I'm using a pivot table. however, I was wondering if there is a way to allow the eventual user to select criteria somehow (such as a specific charge and a specific timeframe) and pull up a record of sorts as you might do in Access?

I'm pretty new to pivot tables and have not worked at all with forms.

Thanks

[ This Message was edited by: eilerc on 2002-03-31 04:32 ]

[Aladin Akyurek]

On 2002-03-30 17:08, eilerc wrote:
I'm building a length of stay study for a county jail and would like to do the entire thing in excel.

I have gathered data regarding in date, out date, time of booking in time of booking out and charges. I need to be able to display data like:

number of assault and battery charges on a given day and how many of these charges stayed in jail 5 - 10 days, 10 - 20 days, etc.

How could I set this up so people could choose the offense and time and get the correct data?

Thanks

Care to post about 10 rows of data, including the labels/column headings along with queries you are interested in?

[Duane]

On 2002-03-31 04:32, eilerc wrote:
Yes - I'm using a pivot table. however, I was wondering if there is a way to allow the eventual user to select criteria somehow (such as a specific charge and a specific timeframe) and pull up a record of sorts as you might do in Access?

I'm pretty new to pivot tables and have not worked at all with forms.

Thanks

[ This Message was edited by: eilerc on 2002-03-31 04:32 ]

AutoFiler can be useful for filtering data according to various queries. It even includes a custom option so you can say "show me only those rows that include data in this column that is less than/greater than/equal to/not equal to..." etc...

See if this helps.

[eilerc]

I'd be happy to post some sample data, but I don't think you can post attachments here.

Could I e-mail the file to you?

Thanks

[Aladin Akyurek]

On 2002-03-31 16:06, eilerc wrote:
I'd be happy to post some sample data, but I don't think you can post attachments here.

Could I e-mail the file to you?

Thanks

Yes, sure.

There is a method though that you can use:

Select an empty cell, type =, select 10 rows of your data including the labels, hit F9, copy what you see, and paste it in the follow up.

Aladin

[Sam40mUK]

I have used the Data, Filter, Advanced Filter to extract data from a list using Criteria. It works but seems like MS defers to Access when things get more involved. Perhaps for your application it could be worth a squint.
Regards
S.