UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Few Queries Into A Single Form, Access 2013    
 
   
Steven Conner
post Aug 16 2019, 02:36 AM
Post#1



Posts: 20
Joined: 9-August 19



Hi All

I have trouble trying to insert few queries into a SINGLE form.

I am trying to insert few queries into a SINGLE FORM so as to consolidate it into 1 page.

All my queries is designed already and it always return a SINGLE row .

I have created a SINGLE form , but I can only select 1 queries only.

Does anyone knows how I can insert few queries into a SINGLE FORM ?

Attached is a pic of what I am trying to do.
Note that all fields from the queries are the same. The red arrows are the queries I wish to insert into a SINGLE form.

Thank you

Steven Conner
Attached File(s)
Attached File  1.PNG ( 206.56K )Number of downloads: 11
 
Go to the top of the page
 
June7
post Aug 16 2019, 04:55 AM
Post#2



Posts: 728
Joined: 25-January 16



Why are there so many queries? Are they all using same table as data source?

What you are trying to do doesn't make sense. Forms are for data entry/edit.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dale.fye
post Aug 16 2019, 07:21 AM
Post#3



Posts: 134
Joined: 28-March 18
From: Virginia


Sounds like what you need is a UNION query to create a recordset which contains values from multiple queries. The syntax for this looks like:
CODE
SELECT Q1.Field1, Q1.Field2, Q1.FieldN FROM yourQuery1 as Q1
UNION ALL
SELECT Q2.Field1, Q2.Field2, Q2.FieldN FROM yourQuery2 as Q2
UNION ALL
SELECT Q3.Field1, Q3.Field2, Q3.FieldN FROM yourQuery3 as Q3

You must ensure that each of these SELECT statements contains the same number of elements. If any element is missing, use a NULL, like:
CODE
SELECT Q1.Field1, Q1.Field2, Q1.FieldN FROM yourQuery1 as Q1
UNION ALL
SELECT Q2.Field1, Q2.Field2, NULL as FieldN FROM yourQuery2 as Q2

You must also ensure that the fields from each of these queries match up by data type (you will get an error if Q1.Field1 is text and Q2.Field2 is numeric).



--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
Steven Conner
post Aug 16 2019, 08:10 AM
Post#4



Posts: 20
Joined: 9-August 19



Hi All

Thanks to All for some of your input.

All the queries is derived from a Single Table consists of over 2000 entries.
I have made about 6 queries from the Same Table.

Each queries has only 1 row of data which has been calculated and the results is acceptable.
Now I need to create a Form where all the queries can be on 1 single form for consolidation.
All of each queries has the same fields and same header as can be seen from the picture “ XBA NG”. Looking from left to right

From the Form I have created, I can only Choose 1 query be it XBA MLG RH or XBA MLG SIDE STR LH....etc taken from the “ Record Source”.
I can’t add more queries from the “ Record Source” as you can see it allows only for 1 query.

The 6 queries is to be placed in the “ Detailed “ section of the Form and I can only add 1 and that is the XBA NG.
I had like to add the other 5 queries just below XBA NG.... and so on until all the 6 queries is stacked in a row format.

The Form I have name is XBA Conso, and I want it to tabulate all the 6 queries into a Single 1 page Form.
That is why I showed the red arrow how I want the other queries to be placed.
so in essence ,if I am successful it should show me 6 rows of the each of the query into 1 Form.

I know what I want ,but I am unable to figure out how it’s to be done.
Basically , the question is “ How to add multiple queries from same table( I have 1 table only.) into a Single Form.

I hope I explained as plainly as possible.
If you see the picture again , you can maybe figure out what I am trying to do.

All my data has no empty (Null) holes.
The 6 queries rows are all filled with data of the same details take from parent Table.

I hope I am not asking for the impossible.

Thanks for reading and appreciate any help whatever help I can get.

Happy weekend,
Cheers to all
Regards,
S Conner.
This post has been edited by Steven Conner: Aug 16 2019, 08:25 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 09:03 AM
Post#5


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


Did you try Dale's suggestion for a UNION query?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Steven Conner
post Aug 18 2019, 09:55 PM
Post#6



Posts: 20
Joined: 9-August 19



Hi All

I am so glad I go the results of what I wanted.

Thanks to Dale and everyone who help me and also GroverParkGeorge for reminding me to look into Dale Union queries.

The union is something new and I learnt a lot about this union queries.

SELECT * FROM [XBA E1]
UNION ALL

SELECT * FROM [XBA E2]
UNION ALL
SELECT * FROM [XBA APU]
UNION ALL
SELECT * FROM [XBA NG ]
UNION ALL
SELECT * FROM [XBA NG DRG STR]
UNION ALL
SELECT * FROM [XBA MLG LH]
UNION ALL
SELECT * FROM [XBA MLG DRG STR LH]
UNION ALL
SELECT * FROM [XBA MLG SIDE STR LH]
UNION ALL
SELECT * FROM [XBA LH SUP STR]
UNION ALL
SELECT * FROM [XBA MLG RH]
UNION ALL
SELECT * FROM [XBA MLG DRG STR RH]
UNION ALL
SELECT * FROM [XBA MLG SIDE STR RH]
UNION ALL
SELECT * FROM [XBA RH SUP STR];


Thank You All
Regards

S Conner
Attached File(s)
Attached File  Pc1.PNG ( 285.54K )Number of downloads: 4
Attached File  Pc1.PNG ( 285.54K )Number of downloads: 4
 
Go to the top of the page
 
cheekybuddha
post Yesterday, 08:53 AM
Post#7


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


@Steven,

If you are still looking at this thread, might you be able to post the SQL of each of the individual queries (eg [XBA E1], [XBA E2], [XBA APU], etc ...)?

I'm still curious whether this could still have been achieved in a single query.

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 02:19 AM