Union Query - Possible For User To Select Tables ?
Jun 28, 2006
I have Access2003 and a problem creating a simple method of constructing a union query.
I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.
I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.
Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?
Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.
I am confident, perhaps without good reason , that this must be possible ....
any help much appreciated.
View Replies
ADVERTISEMENT
May 28, 2006
Please see attached database
If you open the form called people you will see a command button
Called add charge once pressed will open a form called charge
On the Bill To combo Box I have the following Union Select Query
SELECT PrimaryInsurance, PersonID FROM tblpeople UNION Select SecondaryInsurance, PersonID FROM tblPeople
What I want the bill to combo box to display is only the Insurances that are related to the
Patient that the charge is for and not to display other patients insurances
Any Ideas
View 2 Replies
View Related
Oct 9, 2013
I am trying to get a report that shows both column totals and row totals.
I have a crosstab query that gives me the Row totals along with the crosstab data.
I have read on a previous post [URL] ....
That I can union the crosstab to a select query which should get me what I want, but to be honest I am lost.
View 3 Replies
View Related
Aug 20, 2013
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Code:
Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code:
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
View 3 Replies
View Related
Jun 23, 2006
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
Any help greatly appreciated.
View 2 Replies
View Related
Sep 14, 2007
I have the following query:
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper.
Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable
Now when you want to execute the query you get an errormessage.
Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
View 1 Replies
View Related
Jul 8, 2005
I think I need a union query for this. I have 2 tables with the exact same field names. I need a query that will return all of the records together. I haven't been able to figure out how to write the sql code. I do best when I am working from an example, but all the examples I've seen by searching this site are a little confusing...
My two Tables:
Direct Table:
|_Title_|_Number_|_Location_|
|_Youth_|_25212__|__Storage_|
|_Aging_|_93839__|__Stacks__|
EBSCO Table:
|_Title_|_Number_|_Location_|
|Generic|_123456_|__Storage_|
|_Pizza_|_948282_|__Stacks__|
The New Query:
|_Title_|_Number_|_Location_|
|_Youth_|_25212__|__Storage_|
|_Aging_|_93839__|__Stacks__|
|Generic|_123456_|__Storage_|
|_Pizza_|_948282_|__Stacks__|
Fairly simple...but hard enough that I'm having trouble figuring it out. Previously, I was just combining the two tables into a new table, but that means whenever I update information, I have to update it in two places--which defeats the whole purpose of a database.
Thanks,
Siena
View 2 Replies
View Related
Sep 24, 2014
I have 5 tables in access 2010, 4 of them have data in them and I need to populate the 5th table with all data from the 4 tables. I know you have to create a Union query, but i dont know the sql statement
Table 1 - data
table 2 - data
table 3 - data
table 4 - data
table 5 - combine data from table 1, table 2, table 3, table
View 4 Replies
View Related
Oct 10, 2006
I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
View 14 Replies
View Related
Sep 15, 2005
I have a query that requires a selection from my list box "[Forms]![ViewReports]![ListFilterName]"
I have a list box with a UNION so that I can query all contacts. The bound column is one.
SELECT 0,"<<ALL","TEAM MEMBERS>>" from Contacts UNION (SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name] FROM Contacts ORDER BY [Last Name]);
Problems
The list is not ordering by last name.
The query does not assume all contacts when "<<ALL","TEAM MEMBERS>>" is selected.
Where did I go wrong? What's missing?
View 2 Replies
View Related
Oct 24, 2013
I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.
Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000
[code]...
View 1 Replies
View Related
Nov 13, 2006
Hi,
I would like to run a simple select query, where the CRITERIA is based on the user choice.
I have a form with a combo box, with a few choices, and a button that will trigger a macro with one query for now. I can store the choice in to a variable e.g. "town", using Microsoft VB code.
How can I transfer the variable to the CRITERIA field in the query, so whenever I chose a specific "town", my query will select the records for that specific town.
I want to use one query, and I don't want to have to "hard type the criteria in to the cells' query", but I would like to use a variable that can change and will be read it from the user choice through combo box.
If I am not clear enough, please let me know.
Thank you!
View 2 Replies
View Related
Dec 9, 2013
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
View 3 Replies
View Related
Jan 23, 2007
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
View 1 Replies
View Related
Mar 29, 2005
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
View 3 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Oct 4, 2005
hi
is it possible, for any reason, to append another sql query after one sql query?
for example, the first one is "select name from bla bla where name=something" and we just add another query at the end of the query to make something like
"select name from bla bla where name=something and
then show table from this database"
btw i'm using asp to call all these queries..
any help is greatly appreciated..thanks :)
View 4 Replies
View Related
Sep 16, 2007
I have a rotating number of tables that are created from excel spreadsheets that are imported. The Tables will change, but when they are there I need to be able to create a query that will merge them all together so I can run one query against all the tables. In SQL I know you can use a * to say Select *
From Table_1
Is there a syntax for the From portion so I can say:
Select *
From * (AKA all the tables in the Database)?
I have searched for a wildcard for the FROM statement that works like in the select statement but have been unsuccessful at finding an answer. Can anybody help? I'd list the Tables in the from Statement but there are 266 of them. Unless someone knows how to say:
Select *
From All tables in a folder with 266 excel spreadsheets
Thanks for your help
:)
View 1 Replies
View Related
May 21, 2013
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
View 1 Replies
View Related
Feb 5, 2015
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
View 5 Replies
View Related
May 1, 2014
I'm trying to create an access table (with a Primary key) that loads in the data from a Union SQL view.
Im hoping there is a way of achieving this? Ive tried an append and create table queries which copies the data but still no primary key.
I've created a blank table with the fields i want but cant think how to import the fields from the view into it?
Am i better off loading the view into excel?
View 2 Replies
View Related
Jan 4, 2006
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, ""
FROM Table1
UNION SELECT Table2.Column1, "", Table2.Column3
FROM Table2;
Results
NewTable
Column1 Column2 Column3
A...............................3.1.......
A...............................3.2.......
A...............................3.3.......
A...............................3.4.......
A...............2.1.......................
A...............2.2.......................
A...............2.3.......................
A...............2.4.......................
View 14 Replies
View Related
Aug 17, 2007
Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.
for eample if user choose varcurve1
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
if user choose varcurve2
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
is there a way to do that?
Thanks
View 1 Replies
View Related
Jan 15, 2014
I have three check boxes in my form and i want the user to select any one of the check box, if not, i want to display an error msg? How to do it?
View 2 Replies
View Related
May 19, 2006
Hello everyone,
I've made a form where the user can fill in a field ID... and than by clicking on a button another form will be opened with the data of that person (with that ID that's filled in). But if the user fill in an ID that is not currently in the database, there will be also shown another form, but than with no data.. Now I want that if the ID is not known, to display a message or something (like "this ID is not known, try again") and not to open the form..
I've used the following code under the button:
Private Sub openenFormulier_Click()
On Error GoTo Err_openenFormulier_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Onderzoek"
stLinkCriteria = "[Qr_Ant_PG.ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_openenFormulier_Click:
Exit Sub
Err_openenFormulier_Click:
MsgBox Err.Description
Resume Exit_openenFormulier_Click
End Sub
Hopefully someone can help me!
Thanks,
Ankie
View 2 Replies
View Related
Aug 22, 2013
How to proceed and what is the "accepted" version of events.I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.
My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report. So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!
Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...
View 2 Replies
View Related
