Access Question(s)

[thtadthtshldntbe]

Hello.

I have this access database (actually several of them) that I maintain in order with information exported from the EMR that I manage. For the most part producing reports and statistics is ok. I know my way around access, how to produce basic queries for filters and counts, etc. I know just enough about SQL to read it, and to borrow code fragments and redirect them towards some custom queries, etc. And I took a visual basic class years ago, which I might finally have to use (lol).

Anyway, heres the situation.

I have several tables in this one database, that maintain a lot of data with respect to newborn feedings and various situational and clinical criteria.

The two relevant tables here are the master table, which contains a unique record for each child, including medical record number (the primary key) and a discharge date (and other items not necessary to this particular query). The other table is one that contains a list of every feeding for each child.

So (there are many more fields in each table, but the following are the ones in this query)

Table 1
-MedRecNumber
-Discharge Date

Table 2
-Medrecnumber
-date of feeding
-type of feeding
-primary key (just an autonumber field)

All 4 tables in this database are linked one to many from table 1, with the medical record number being the linked item.

So, I need to produce a query that does the following.

-Pulls up all entries relevant to a given time frame (I got this)
-Finds all entries (ie feedings that occur withing 24 hours of discharge
(essentially I do

Field - [Table1]![newborndischargedate] - [Table2]![Newbornfeedingtime]
Total - Expression
Criteria - <1

And that works.

So I get all the feedings for a given date range, that occur in the last 24 hours of discharge.

Now the final step has me somewhat flummoxed.

The purpose of the query is ultimately to produce a count of "exclusively breastfeeding babies".

Now, the query at this point has a list of all the feedings, by all the babies (given that its for a time range and less than 24 hours, which I won't type anymore).

Each record would have

medrecnumber, feeding time, method of feeding

Basically I run a check on all feedings for a given baby, and if it was never bottlefed, then that would increase the count for exclusively breastfed, by 1.

I remember enough visual basic, to sort of piece my way through this, though as it turns out, I remember more C++, lol and was doing my crappy little code like it was c++, but I am sure that I can work my way through this in VBA, it just might take time.

What I want to know is am I going in the right direction.

Would that VBA code work as intended, ie if I put it in the rightmost query column, or rather call it, would it run its count on the records already sorted and filter by the previous criteria.

And then, from a logical point of view is the following correct in the VBA (not the coding but my methodology)(I know I have to declare variables and set up the function inside a module).
---------------------------------
do

do
if
type of feeding = "bottle"
then
exclusive = "no"
while Medreccurrentnum = Medrecprevnum

if exclusive <> "no"

then exclbf = exclbf +1

exclbf = "yes"
until (condition which means there are no more records to look at)

-----------------

Note I am not sure about the condition for the until part, but I am sure I can figure that out, with some research.

Am I overthinking this problem? Is there some simpler way in access to do this in a query?


I enjoy the challenge of this work (I am switching my degree from biomed/electronics to Healthcare informatics as a result of this job) because it is pushing me into new territories. I know I ultimately have to learn SQL and at least get to an intermediate level with VBA

 

[KLin]

I would create a query that would give me distinct medical record numbers where the baby was bottlefed, then filter out any of those medical record numbers by a join or a not in clause for the feedings that fall within the given date range.

I'm assuming that if the baby is fed even once from a bottle, it should be excluded from the count, yes? Even if it falls outside the date range criteria?

 

[thtadthtshldntbe]

No. That is one of the debates ongoing with the clinical people.

One of the criteria for declaring a child exclusively breastfed is so long as the baby is breastfed and only breastfed for the last 24 hours before discharge (healthy babies get a 2 or 3 day stay with their mommy, depending on the mother's delivery type, and due to circumstances, it is sometimes not possible to breast feed the baby early on after delivery, so the clinicals are debating, excluding more couplets and going to only breast fed from birth for example as being exclusively breastfed.

Ignore this particular point in this paragraph for purposes of this thread, because I already know how to account for it. Basically, its possible to have a baby get a bottlefeeding, but from breast milk. I can account for that already, or will in Decembers stats, by adding another field to the export from the EMR and filtering by it.

The issue is this.

The feeding database might have feedings for 3 different children for example (actually over 200 children so far, it already has more than 5000 records).

MR# -- Feeding type --- Feeding time

0001-- bottle
0001-- breast
0001-- breast
0001-- breast
0002-- bottle
0002-- bottle
0002-- bottle
0003-- breast
0003-- breast

you can see from the date, that only baby 0003 is exclusively breast fed from the beginning

but its also possible for baby 0001 to qualify depending on that 24 hour criteria.

So what I need is a query that looks at all the entries for a given babies feedings and only increments the counter if it qualifies under whatever the clinicals decide on as the criteria for exclusive breastfeeding. Its the counting records by individual baby that I am flummoxed on.

 

[KLin]

Have a calculated field that assigns a value of 1 to records where the baby was fed by bottle.

In another query, group by medical record number. If the sum of the calculated field is equal to 0, put in a 1 else put in a 0. Then sum it up to get the count of babies exclusively breast fed in a 3rd query.

 

[thtadthtshldntbe]

Ok that seems doable.

My only question at this point is the calculated field. I was playing around with this and I was thinking that I would need to use an if/then statement to produce the value in the calculated field, but I don't think you can unless you use a vba function.

 

[KLin]

http://www.techonthenet.com/access/functions/advanced/iif.php

 

[thtadthtshldntbe]

thanks bro, sorry to seem like such an idiot.

I was writing something in vba, which sort of worked. That would be simpler.

 

[thtadthtshldntbe]

thanks. It works.

I made the stupid mistake of inverting the logic in the IIF statement (breast being 1 not zero). But once I figured that out, it was ok.

Now I am exporting one more datafield from the EMR, which is when the babies are bottle fed, but with breast milk, and that will be easy to incorporate.

 

[Tweak155]

thanks. It works.

I made the stupid mistake of inverting the logic in the IIF statement (breast being 1 not zero). But once I figured that out, it was ok.

Now I am exporting one more datafield from the EMR, which is when the babies are bottle fed, but with breast milk, and that will be easy to incorporate.

I would still recommend VBA to solve these types of problems, otherwise you will continually need to create lots of queries to solve your problems and eventually this will catch up to you.

Here is some basic VBA code you can probably extrapolate a solution from:

Dim RS As Recordset, filterRS As Recordset, subFilterRS As Recordset

Set RS = CurrentDB.OpenRecordset("QUERY HERE IN tSQL FORMAT")

If RS.EOF Or RS.BOF Then
'recordset is empty - no results
Else
    'We have some results, let's filter!
    RS.Filter = "FILTER CONDITION, EX: FieldName = 'SomeVal'"
    'Now we capture the result in the filter RS
    Set filterRS = RS.OpenRecordset
    'RS now has the original data, and filterRS has the "failed filter" data removed
    'Get some data!
    filterRS.RecordCount 'How many results?
    filterRS.MoveFirst 'Go to the first record
    filterRS.MoveNext 'Go to the next record from current location
    filterRS.MoveLast 'Guess!!
    
    'Let's loop through the data!
    With filterRS
        If Not (.EOF Or .BOF) Then 'Make sure we have data!
            .MoveFirst 'Make sure we start at the beginning
            While Not .EOF 'Go until the last record
                'Do some stuff!
                'Some stuff you can do...
                filterRS("Field1") 'Access data on the current record from the field named "Field1"
                .Fields("Field1") 'Equivalent to above
                .Fields(0) 'Access field found at location 0
                .Fields(0).Name 'What's the name of Field at location 0?
                NZ(.Fields(0), "IT'S NULL!") ' Check to see if the field has a NULL value, if so, return "IT'S NULL!" (you can return anything, numbers, text, etc). Otherwise, return whatever is found at Field 0
            .MoveNext 'Very important - without this you will infinite loop!
            Wend 'End of While loop
        End If
    End With
End If

Anyway, I'm writing this outside of the IDE so some of my syntax might be off. But hopefully you get the picture.

 

[thtadthtshldntbe]

yeah i got that from the triple query I had to build, which will grow slightly more complex when I correct for the additional data fields I have to add in. VBA is likely the way to do these things going forward.

I have to start using what I learned in that VB class from years ago. Though it was more centered towards build applications from the IDE, rather than adding custum modules to Access. It should be easy to adapt. If anything this job is a challenge in a good sense.

 

[thtadthtshldntbe]

Have a calculated field that assigns a value of 1 to records where the baby was fed by bottle.

In another query, group by medical record number. If the sum of the calculated field is equal to 0, put in a 1 else put in a 0. Then sum it up to get the count of babies exclusively breast fed in a 3rd query.

Ok, everything worked fine, as I mention before. Then I went into the base query to make the IIF statement more complex and therefore the data more accurate.

I have this statement as the field line
BFVal: IIf([BF Newborn Feeding Data]![Feeding Method]="breast",0,1))

and it worked perfectly.

I then changed it to this

BFVal: IIf([BF Newborn Feeding Data]![Feeding Method]="breast",0,IIf(([BF Newborn Feeding Data]![Feeding Method]="bottle" And [BF Newborn Feeding Data]![Formula Type]="EBM"),0,1))

and I get the error

"you tried to execute a query that does not include the specified expression BFVal: IIf([BF Newborn Feeding Data]![Feeding Method]="breast",0,IIf([BF Newborn Feeding Data]![Feeding Method]="bottle" And [BF Newborn Feeding Data]![Formula Type]="EBM",0,1)) as part of an aggregate function."

And I have no clue what is the problem. I have done nested IIF statements before in Access with no problem.

What am i missing?

 

[KLin]

Is Formula Type a column in your query? If not, you need to add it so access knows it should group by formula type as well.

Otherwise, you need to remove the aggregation (summations, averages, etc. etc) from this query and perform them in another query

 

[thtadthtshldntbe]

Formula Type is another field..

damn, i feel like an idiot again. As soon as I read your response it made sense. I forgot to add it to another column.

Sorry bro. Maybe this comes from having to do 17 different things at once at work. I try to squeeze time in on this database, around pretty much everything else.