M
Mark Woodward
Hi
I've probably bitten off more than I can chew ( I'm new to access ) but
I'll ask anyway. I'm wondering what would be the best way to attack this
problem. I've just started 3 months temp work in the IT support area of a
large hospital's pathology dept. They use a particular laboratory system
that utilises a faircom db to store results etc. Somehow ( and i'm not sure
how at this point ) they can pull out say a months data into access as one
ungainly table ( ie not normalised, just dumped data). As an indication last
month had 318,000 odd records. People who have looked at this previously
have written a lot of specific queries to pull data out:
eg a query to find all FBC ( full blood count ) results. Another for Na (
sodium ), another for K ( potassium ) etc etc etc.
Considering there's hundreds of individual tests, that approach seems a bit
painful. Also for each of those tests you might want only the urgent ones,
ones from a particular lab, those for a certain date etc.
I created a form using 3 combo boxes, a listbox and a graph.
I thought I'd get smart and use combo boxes within the sql like this: ( cbo
= a combo box )
SELECT episode, [date added], [avail date] FROM foo
WHERE urgent=cbo1, lab=cbo2, test =cbo3
( along those lines anyway. I cant remember the exact syntax ( I'm not at
work at the moment)).
This is working great!! ie I get the right records displayed in the listbox.
If I change one of the comboboxes the list is updated to reflect the change.
Great!!
(This is all driven by 1 query)
This is where I lose it!!
Questions:
1 - What I'd like to be able to do is graph *only* the records in the
listbox and have the graph change ( as the listbox does ) when I change the
comboboxes. Is this possible? Currently the graph shows *all* records
associated with the underlying query not just the records in the listbox (
ie the ones found using the comboboxes). Basically I'm trying to graph the
turn around times ( time a specimen arrives compared to time the results for
that specimen are authorised) for each hour of a 24hr period to see if
there's a 'slow' period that may mean more people working at that time in
the future.
eg between 2 and 3 PM there might have been 40 full blood counts done with
an average turn around time of 45 minutes. Between 3 and 4 PM there might
have been 38 full blood counts done with an average turn around time of 1hr
30min.
2 - Is there a better approach in general?
The 'question' I want to ask the mother of all tables is:
" For all of your 318,000 odd records, show me those for 'this test' done
in 'this lab' and only the 'urgent' ones."
The list box is behaving, but how to create a dynamic graph?
3 - should / can I save the data in the listbox as a new table and graph
that?
thanks,
Mark
I've probably bitten off more than I can chew ( I'm new to access ) but
I'll ask anyway. I'm wondering what would be the best way to attack this
problem. I've just started 3 months temp work in the IT support area of a
large hospital's pathology dept. They use a particular laboratory system
that utilises a faircom db to store results etc. Somehow ( and i'm not sure
how at this point ) they can pull out say a months data into access as one
ungainly table ( ie not normalised, just dumped data). As an indication last
month had 318,000 odd records. People who have looked at this previously
have written a lot of specific queries to pull data out:
eg a query to find all FBC ( full blood count ) results. Another for Na (
sodium ), another for K ( potassium ) etc etc etc.
Considering there's hundreds of individual tests, that approach seems a bit
painful. Also for each of those tests you might want only the urgent ones,
ones from a particular lab, those for a certain date etc.
I created a form using 3 combo boxes, a listbox and a graph.
I thought I'd get smart and use combo boxes within the sql like this: ( cbo
= a combo box )
SELECT episode, [date added], [avail date] FROM foo
WHERE urgent=cbo1, lab=cbo2, test =cbo3
( along those lines anyway. I cant remember the exact syntax ( I'm not at
work at the moment)).
This is working great!! ie I get the right records displayed in the listbox.
If I change one of the comboboxes the list is updated to reflect the change.
Great!!
(This is all driven by 1 query)
This is where I lose it!!
Questions:
1 - What I'd like to be able to do is graph *only* the records in the
listbox and have the graph change ( as the listbox does ) when I change the
comboboxes. Is this possible? Currently the graph shows *all* records
associated with the underlying query not just the records in the listbox (
ie the ones found using the comboboxes). Basically I'm trying to graph the
turn around times ( time a specimen arrives compared to time the results for
that specimen are authorised) for each hour of a 24hr period to see if
there's a 'slow' period that may mean more people working at that time in
the future.
eg between 2 and 3 PM there might have been 40 full blood counts done with
an average turn around time of 45 minutes. Between 3 and 4 PM there might
have been 38 full blood counts done with an average turn around time of 1hr
30min.
2 - Is there a better approach in general?
The 'question' I want to ask the mother of all tables is:
" For all of your 318,000 odd records, show me those for 'this test' done
in 'this lab' and only the 'urgent' ones."
The list box is behaving, but how to create a dynamic graph?
3 - should / can I save the data in the listbox as a new table and graph
that?
thanks,
Mark