Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-24-2009, 05:35 AM   #1
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
filter which queries are used in union query

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.

shenty is offline   Reply With Quote
Old 06-24-2009, 06:11 AM   #2
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 06-24-2009, 06:20 AM   #3
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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

shenty is offline   Reply With Quote
Old 06-24-2009, 06:41 AM   #4
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 06-24-2009, 06:55 AM   #5
Atomic Shrimp
Humanoid lifeform
 
Join Date: Jun 2000
Location: Portsmouth, England
Posts: 1,954
Thanks: 0
Thanked 8 Times in 7 Posts
Atomic Shrimp has a spectacular aura about Atomic Shrimp has a spectacular aura about
Re: filter which queries are used in union query

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
- it's not radioactive, and contains only a few crustaceans.
Atomic Shrimp is offline   Reply With Quote
Old 06-24-2009, 07:23 AM   #6
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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 is offline   Reply With Quote
Old 06-24-2009, 07:39 AM   #7
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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
Attached Files
File Type: zip filtered history temp.zip (147.8 KB, 60 views)
File Type: zip Cubley Animal Database (Data Only).zip (254.8 KB, 56 views)

shenty is offline   Reply With Quote
Old 06-24-2009, 07:55 AM   #8
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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
shenty is offline   Reply With Quote
Old 06-24-2009, 11:58 PM   #9
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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
Quote:
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
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 06-25-2009, 01:59 PM   #10
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Smile Re: filter which queries are used in union query

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.
shenty is offline   Reply With Quote
Old 06-25-2009, 11:24 PM   #11
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 06-26-2009, 01:29 AM   #12
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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.
shenty is offline   Reply With Quote
Old 06-26-2009, 01:45 AM   #13
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 06-26-2009, 05:00 AM   #14
shenty
Newly Registered User
 
Join Date: Jun 2007
Posts: 117
Thanks: 0
Thanked 0 Times in 0 Posts
shenty is on a distinguished road
Re: filter which queries are used in union query

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 cock !

Any ideas.

Cheers again
shenty is offline   Reply With Quote
Old 06-26-2009, 05:43 AM   #15
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: filter which queries are used in union query

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

__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Union Query Help Please rede96 Queries 3 01-25-2007 05:37 AM
The Union of 2 Queries cross5900 Queries 2 10-06-2006 09:12 AM
Report parameter query filter issues cortevil Reports 7 07-31-2006 06:19 AM
[SOLVED] union query help stv75 Queries 1 06-05-2003 03:44 PM
Union Queries Converting Fields to Text mstorer Queries 2 08-29-2002 12:16 PM




All times are GMT -8. The time now is 02:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World