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.