filter which queries are used in union query (1 Viewer)

shenty · Jun 24, 2009

I have a problem that i don't know how to explain properly. I have a union query that pulls data from 8 other queries/tables to form a history of any given animal.

I want to create a form with tick boxes to select which queries to include in the union query so that i can customise the output of the report.

My union query is :

Code:

SELECT [DOB]AS[DATE],[DAM]AS[ID],"Calved" AS[Expr1003],[String] AS[DETAIL]
FROM [HistoryCalving]
UNION ALL SELECT [AIDate],[TAG],"Bulling",[String]
FROM [HistoryBulling]
UNION ALL SELECT [DOB],[TAG],"Born",[String]
FROM [HistoryDOB]
UNION ALL SELECT [Purchase Date],[TAG],"Purchased",[String]
FROM [HistoryPurchased]
UNION ALL SELECT [Movement Date],[TAG],"Moved Off",[String]
FROM [HistoryMovement]
UNION ALL SELECT [AIDate],[TAG],"AI",[String]
FROM [HistoryAI]
UNION ALL SELECT [Footcare Date],[TAG],"Footcare",[String]
FROM [HistoryFootcare]
UNION ALL SELECT [AdminDate],[AnimalIdentification],"Medicine",[String]
FROM [HistoryMedicine]
ORDER BY [DATE];

This is the HistoryAI query which the union query calls:

Code:

SELECT qryAIRegister.AIDate, qryAIRegister.TAG, (IIf(Left([Action],9)="PD Test +",[Action] & " " & Left([Notes],2) & " Weeks Approx. " & [Notes],[Action] & " (" & [AIBull] & " " & [AIBullBreed] & ") " & [Notes])) AS [String], qryAIRegister.AI_ID, qryAIRegister.AIorBull AS Expr1, qryAIRegister.Notes
FROM qryAIRegister
WHERE (((qryAIRegister.AIorBull)="AI"))
ORDER BY qryAIRegister.AIDate, qryAIRegister.AI_ID;

The HistoryPurchased:

Code:

SELECT AnimalRegister.[Purchase Date], AnimalRegister.TAG, AnimalRegister.[Purchased From], AnimalRegister.PurchasePrice, [Purchased From] & (IIf(IsNull([PurchasePrice]),""," - " & "£" & [PurchasePrice])) AS [String]
FROM AnimalRegister
WHERE (((AnimalRegister.[Purchase Date]) Is Not Null));

I think you'll get the idea...

I have a listbox that opens the report when double clicked. The reports record source is qryUKHistory which is this:

Code:

SELECT History.ID, History.DATE, History.DETAIL, History.Expr1003, AnimalRegister.Brand, AnimalRegister.Sex, AnimalRegister.Breed, AnimalRegister.DOB, AnimalRegister.DAM, AnimalRegister.[On Farm], AnimalRegister.Homebred, AnimalRegister.[Purchase Date], AnimalRegister.[Movement Date], AnimalRegister.[Moved To], AnimalRegister.PurchasePrice, AnimalRegister.SoldPrice, AnimalRegister.InCalf, AnimalRegister.NextDue
FROM AnimalRegister INNER JOIN History ON AnimalRegister.TAG = History.ID
WHERE (((History.ID)=[forms]![frmSearchHistory]![txtSelect]))
ORDER BY History.ID, History.DATE;

Sorry if i have not explained the problem very well, i can upload the db if required.

Any suggestions would be great.

DCrake · Jun 24, 2009

When creating union queries you are effectively sitting one query on the top of another and as such the most important thing to do is to ensure that the same number of columns are returned from each union. Think of it as a stack of building bricks. If you have 5 bricks in the first row then try and place 7 bricks on top of this two won't fit. They wonlt stack properly. Conversley if you put 7 on the bottom then 5 on the second then try and put 7 on the third row you will get the same result.

You can simulate columns by giving them a false value.

Select F1 As fld1, F2 as fld2, F3 as fld3, F4 as fld4 From Table 1
Union Select " " as fld1, X2 as F2, X3 As fld3, X4 as fld4 From Table2

etc

As you can see each uion has the same number of columns and uses the same column headings even theough the actual fields do not match.

David

shenty · Jun 24, 2009

I have the correct number of fields and the history report works fine but i want to be able to chose which queries to include in the union query.

ie sometimes i might want AI & Calving History, or AI & Bulling, or Footcare and Medicine etc etc.

Is there a way, in the union query of only including queries if a box on a form is ticked ?

Many thanks

DCrake · Jun 24, 2009

Yes:

You will need a button that fires off the building of the union query.

Lets say your form has a series of tick boxes each relating to a specific table.

Now in your union query you have an extra field that is an indicator as to the source table. Lets say we give them all a letter A,B,C,D etc

Now when you click on your run button you will need to build up a string based on the selections made

If Me.Chk1 = True then
strItems = "A, "
End If

If Me.Chk2 = True then
strItems = strItems & "B, "
End If

And so on for each check box on form. In the end we may end up with

strItems = "A, C, D, E, F,Z, "

Now we need to drop the last comma

strItems = Left(strItems,(Len(strItems)-2)

Now we create the where condtion using the In() function

Select * From YourUnionQuery Where SourceTableLetter In(" & strItems & ")"

So what happens now is that it will restrict the ouput to only those tables that the user has indicated on the screen.

David

Atomic Shrimp · Jun 24, 2009

This might not be the best time to raise the point, but it looks like your data might be in dire need of normalizing.

In a nutshell, the results of the union query you're trying to build - that's how you probably ought to be storing your data - in one table, with a column describing what kind of data is on each row.

shenty · Jun 24, 2009

DCrake

Not sure that i understand how i'd implement that into my db but i understood 95% of what you wrote (code wise at least).

Mike

I'm not convinced my db is not normalised correctly, it has kind of evolved and developed over a long period of time and i have considered starting fresh a few times but that would be a daunting taks.

If its ok i'll strip out the stuff that doesn't need to be there and upload it. If either of you don't mind. That way one can tell me whether it needs normalising and the other could suggest how i'd 'build' the union query.

Thanks again - i'll upload it shortly.

shenty · Jun 24, 2009

Hi

I've attached the stripped down front end & the data seperate as it uses link tables. The data will need extracting to c:\

Would you be able to guide me a little more now at all ?

Many thanks

shenty · Jun 24, 2009

DCrake

I keep reading over you reply to try to understand it. I may have got there !!! Am i right in saying.......

Create and extra column in the union query for the 8 tables/queries. Place text "A", "B", "C" etc in each one. So that if the query was ran at that stage then the first batch of records would have "A" in end column, the next batch "B" in end column etc.

Then build the string with the tick boxes & form (i get that no problem).

The bit i think i'm not understanding is:
Select * From YourUnionQuery Where SourceTableLetter In(" & strItems & ")"

Does this go in vb behind the button or in the union query itself, if in the query then where !!!

Oh and is SourceTableLetter the added column i did in step 1 ?

Am i getting there ?

Best regards

DCrake · Jun 25, 2009

Lets work with an example

Example 1:Creating the union query using SQL

Assumtions:

The first set of data is always selected

3 optional tick boxes on your form Tick1, Tick2, Tick3

Command button named CmdBuildSql

Code:

Sub CmdBuildSql_OnClick()

Dim sSql As String

sSql = "SELECT F1 As fld1, F2 As fld2, F3 As fld3, 'A' As Tbl FROM Table1"

If Me.Tick1 = True Then
   sSql = sSql & "UNION ALL SELECT B1 As fld1, B2 As fld2, B3 As fld3, 'B' As Tbl FROM Table2"
End If

If Me.Tick2 = True Then
   sSql = sSql & "UNION ALL SELECT C1 As fld1, C2 As fld2, C3 As fld3, 'C' As Tbl FROM Table3"
End If

If Me.Tick3 = True Then
   sSql = sSql & "UNION ALL SELECT D1 As fld1, D2 As fld2, D3 As fld3, 'D' As Tbl FROM Table4"
End If


Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset(sSql)


etc

End Sub

Example 2: Creating a filte on an existing union query

Union Query SQL

SELECT F1 As fld1, F2 As fld2, F3 As fld3, 'A' As Tbl FROM Table1
UNION ALL SELECT B1 As fld1, B2 As fld2, B3 As fld3, 'B' As Tbl FROM Table2
UNION ALL SELECT C1 As fld1, C2 As fld2, C3 As fld3, 'C' As Tbl FROM Table3
UNION ALL SELECT D1 As fld1, D2 As fld2, D3 As fld3, 'D' As Tbl FROM Table4

Code:

Sub CmdBuildSql_OnClick

Dim strItems As String

If Me.Tick1 = True Then
   strItems = "A, "
End If

If Me.Tick2 = True Then
   strItems = strItems & "B, "
End If

If Me.Tick3 = True Then
   strItems = strItems & "C, "
End If


strItems = Left(strItems,Len(strItems)-2)

Dim sSql As String
Dim Rs As DAO.Recordset

sSql = "Select * From UnionQueryName Where Table In(" & strItems & ")"

Set Rs = CurrentDb.OpenRecordset(sSql)

etc

End Sub

shenty · Jun 25, 2009

it seems i understood pretty well. Thanks very much for that. A little tweaking adding ' around the a,b,c etc and i got the string working. My problem now is .....how on earth do i get the report to use this recordset. I've added do.cmd openreport but the reports record source seems to be using the query that is not filtering. ! Sorry if that doesn't make sense but i'm on my mobile writing this and can't find an enter key. ! Basically i need to know how to open the report with this new recordset. Thanks again for your help.

DCrake · Jun 26, 2009

Before delving deeper into vba lets try this approach.

Create a public string variable in a module called strRptSource

Next on your form where you have built the sql string sSql

Add this line immediately after

strRptSource = sSql

Next go into the design of the report you want to print and go into the OnLoad event of the report and type in the following

Code:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = strRptSource
End Sub

This should reset the record source to the filtered union query.

David

shenty · Jun 26, 2009

Brilliant !!!

I took the Set rd = CurrentDb.OpenRecordset(sSql) out of it as it was giving me an error and BINGO.....my report just as i wanted it.

Thanks for your help on this one, my learning curve in access just keeps going up.

Now i need to work out how to turn my listbox into a multi-select one so i don't have to do one at a time.

Thanks again for your help DCrake.

DCrake · Jun 26, 2009

What you need to do there is to set the multi select option to simple then

on your command button you need to create a loop based on the code you are currently running by passing the value of the item selected in the list box to the function.

Code:

For Each vItem In Me.LstBox.ItemSelected

      Run the main code here

Next

If you type in ItemSelected in to vba highlight and press F1 for examples.

David

shenty · Jun 26, 2009

Oh dear, things were going so well, now i've got things all knotted up !!!

If i leave the listbox as NO multiselect, everything works, Because there is a filter in the query [forms]![frmSearchHistory]![txtSelect]. My string is built correctly, the report opens, the record source is updated and the report previews just fine.

I figured that if i removed the filter from the query when i changed the listbox to a multiselect and include the loop as suggested then things should work......they don't.

The report opens but it is listing everything & its all to **** !

Any ideas.

Cheers again

DCrake · Jun 26, 2009

Send me the code you have got.

I am thinking that when you click on an item from the listbox you are populating the txtselect control with the listbox item, correct?

If that is the case if you pass each item in turn from the list box to the control then do a repaint/requery on the form/control then call the other code it should respond accordingly.

Going off air in a short while.

David

shenty · Jun 26, 2009

I've attached the db, hope thats ok.

The listbox is working ok if not multiselect. I'm after a multiselect where it prints a report for each item selected, showing only the tick boxes ticked. (Page break between each item selected too).

Thanks again for looking at this for me.

(The data needs to be in c:\)

DCrake · Jun 26, 2009

I am about to go off line will take a look over the weekend and get back to you.

David

shenty · Jun 26, 2009

great, thanks mate.

DCrake · Jun 28, 2009

Have made some changes to the forms an queries. See revised version.

David

shenty · Jun 29, 2009

Superb. That is exactly what i was after.

I like the way you've built up a string for looking in the ID field OR used the txtSelect box. Thats very nifty.

I'm sure i can incorporate this now into my db. I need to tweak the report output somewhat but the process of being able to print multiple reports from a multiple list box has evaded me until now.

That is a brilliant example, the time you have afforded to this is very greatly appreciated.

filter which queries are used in union query (1 Viewer)

Registered User.

Remembered

Registered User.

Remembered

Humanoid lifeform

Registered User.

Registered User.

Attachments

Registered User.

Remembered

Registered User.

Remembered

Registered User.

Remembered

Registered User.

Remembered

Registered User.

Attachments

Remembered

Registered User.

Remembered

Attachments

Registered User.

Similar threads

Users who are viewing this thread