Indexing Query

[Cranky]

Hi

I've been messing around trying to change some stats sheets from Excel
to an Access equivalent.

The information would be in a columnar form, with 5 columns and 13
rows, 65 fields in all. When I first set this up, I kept getting error
messages because of duplicate records. I then went through and changed
each field to Duplicates OK. This in turn brought up the message that
there were too many indexed fields.

Although I'm only playing with the system, I am interested in how to
resolve this because it may help with something else I'm working on.
The only way I could think of was to have 1 table for each field, eg
0830 - 1000, waiting <5 minutes = 1 textbox; 0830 - 1000, waiting time
6 - 10 minutes = another textbox.

Is there a better, more sensible solution to this?

Steve

 

[Cranky]

The only way I could think of was to have 1 table for each field, eg

0830 - 1000, waiting <5 minutes = 1 textbox; 0830 - 1000, waiting time
6 - 10 minutes = another textbox.

uh... 'textbox' above should read 'table.' Cuh.

Steve

 

[Joseph Meehan]

Hi

I've been messing around trying to change some stats sheets from Excel
to an Access equivalent.

The information would be in a columnar form, with 5 columns and 13
rows, 65 fields in all. When I first set this up, I kept getting error
messages because of duplicate records. I then went through and changed
each field to Duplicates OK. This in turn brought up the message that
there were too many indexed fields.

Although I'm only playing with the system, I am interested in how to
resolve this because it may help with something else I'm working on.
The only way I could think of was to have 1 table for each field, eg
0830 - 1000, waiting <5 minutes = 1 textbox; 0830 - 1000, waiting time
6 - 10 minutes = another textbox.

Is there a better, more sensible solution to this?

Steve

I think we need to start by looking at the data that you are saving. It
looks very much like you need a relational data design. Access stores and
works with data far different than Excel.

 

[Cranky]

There would be five time bands during the day: 0830 - 1000, 1001 -
1200, 1201 - 1400, 1401 - 1600 and 1601 - 1715.

There would be 13 rows in each time band to record the total duration
of each visit. Each row corresponds to visit duration in increments of
5 minutes: 1 - 5 minutes, 6 - 10, 11 - 15, 16 - 20 and so on, up to 61+
minutes.

I know this is a straightforward Excel spreadsheet - which is how it is
recorded now - but I just wondered how it could be done in Access.

Thanks

Steve

 

[Joseph Meehan]

There would be five time bands during the day: 0830 - 1000, 1001 -
1200, 1201 - 1400, 1401 - 1600 and 1601 - 1715.

There would be 13 rows in each time band to record the total duration
of each visit. Each row corresponds to visit duration in increments of
5 minutes: 1 - 5 minutes, 6 - 10, 11 - 15, 16 - 20 and so on, up to
61+ minutes.

I know this is a straightforward Excel spreadsheet - which is how it
is recorded now - but I just wondered how it could be done in Access.

Thanks

Steve

I have read your message a number of times since you posted it, and
every time I end up the same way, no answer.

Currently I have a shoulder problem and I am in some pain. Every time I
try to understand what you wrote, it seems to start up and I just can't get
your information into my head. Since I expect a couple of months of this, I
fear I am not going to be able to offer you any answers.

Maybe you can start a new thread. Some others who may have been able to
answer your questions may have passed over it foolishly thinking I could
handle it.

Best of luck. Sorry.

 

[corey lawson]

I have read your message a number of times since you posted it, and
every time I end up the same way, no answer.

Currently I have a shoulder problem and I am in some pain. Every time I
try to understand what you wrote, it seems to start up and I just can't get
your information into my head. Since I expect a couple of months of this, I
fear I am not going to be able to offer you any answers.

Maybe you can start a new thread. Some others who may have been able to
answer your questions may have passed over it foolishly thinking I could
handle it.

Best of luck. Sorry.

Here's one way to do it.

Create a table (starttimebands) with your bands in it:
band start end
1 0000 0829
2 0830 1000
3 1001 1200
4 1201 1400
5 1401 1600
6 1601 1715
7 1716 2359

You don't need to do the bands for the minutes. That's easy enough to
calculate ( the Mod function).

I'm assuming your data is kind of like this and called Duration_Data:

entity starttime duration_mins
1 0835 35
2 1205 65
....

Now, to get your query to get the right start time band, then you would
do a "theta" join:

select d.*, b.band, iif(d.duration_mins >= 60, 13, d.duration_mins mod
5) as duration_band
from duration_data d join starttimebands as b on d.starttime >= b.start
and d.starttime <= b.end