Combination of two queries

R

radek225

Registered User.
Local time
Today, 02:31
Joined
Apr 4, 2013
Messages
307
Is it possible to do combination of these two queries?

SELECT * FROM tblGoraZleceniaNowaWyceny WHERE [id_wycena_pre] = Forms!frmWycenyObszarroboczy!ID_wycena_pre[/CODE]

and

Code: 
SELECT [Query1].[NumerArkusza], [Query1].[nazwa], [Query1].[id_wycena_pre] 
FROM Query1  
WHERE ((([Query1].[numerarkusza]) In (SELECT [numerarkusza] FROM [Query1] As Tmp 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 )));
I need to do this to work with Recordset.
 
Last edited:
You having a bad Friday headache? Or are you serious?

Code: 
strSQL = " SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[nazwa] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[id_wycena_pre]" & _ 
         " FROM tblGoraZleceniaNowaWyceny " & _ 
         " WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza]) In ( " & _ 
                    " SELECT [numerarkusza] " & _ 
                    " FROM [tblGoraZleceniaNowaWyceny] As Tmp " & _ 
                    " GROUP BY [numerarkusza] " & _ 
                    " HAVING Count(*)>1 )))" & _ 
         "   AND [id_wycena_pre]=" & Forms!frmWycenyObszarroboczy!ID_wycena_pre
 
Last edited:
Do the two seperate queries work?

What do you mean count replications from all table? by adding the "and id_wycena_pre = 123" you should only get that one record

can also try
Code: 
strSQL = " SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[nazwa] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[id_wycena_pre] " & _ 
         " FROM tblGoraZleceniaNowaWyceny " & _ 
         " WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza]) In ( " & _ 
                    " SELECT [numerarkusza] " & _ 
                    " FROM   [tblGoraZleceniaNowaWyceny] As Tmp " & _ 
                    " WHERE  [id_wycena_pre]=" & Forms!frmWycenyObszarroboczy!ID_wycena_pre & _
            	    " GROUP BY [numerarkusza] " & _ 
                    " HAVING Count(*)>1 )))"
 
namliam said:
You having a bad Friday headache? Or are you serious?

Code: 
strSQL = " SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[nazwa] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[id_wycena_pre]" & _ 
         " FROM tblGoraZleceniaNowaWyceny " & _ 
         " WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza]) In ( " & _ 
                    " SELECT [numerarkusza] " & _ 
                    " FROM [tblGoraZleceniaNowaWyceny] As Tmp " & _ 
                    " GROUP BY [numerarkusza] " & _ 
                    " HAVING Count(*)>1 )))" & _ 
         "   AND [id_wycena_pre]=" & Forms!frmWycenyObszarroboczy!ID_wycena_pre
Click to expand...

It doesn't work.

Query shows result for Forms!frmWycenyObszarroboczy!ID_wycena_pre - and it's ok. BUT count replications from all table "tblGoraZleceniaNowaWyceny" so as a result in my example I have two rows for [id_wycena_pre] = 68. First row [NumerArkusza] = 1 and second [NumerArkusza] = 2. So it's wrong because numbers 1 and 2 isn't the same.


In your second string. query shows only replication (it's correct) BUT from all table. So if I have in my form [NumerArkusza] = 1 and [NumerArkusza] = 2 query shows nothing - it's ok. BUT If I have [NumerArkusza] = 1 and [NumerArkusza] = 1, query shows result from all table no matter [id_wycena_pre] - So it also is wrong
 
Last edited:
Yes separate queries work. But I need one to work with recordset
 
namliam said:
Do the two seperate queries work?

What do you mean count replications from all table? by adding the "and id_wycena_pre = 123" you should only get that one record
Click to expand...

I mean. Query looking for in ALL RECORDS in my table some replications for [NumerArkusza]. For every row in my form where [id_wycena_pre] = 123 - wrong

Query should looking for replications [NumerArkusza] in my table but ONLY WHERE [id_wycena_pre] = 123 for every row in my form where [id_wycena_pre] = 123
 
radek225 said:
So it's wrong because numbers 1 and 2 isn't the same.
Click to expand...
Why would it be wrong? You are getting duplicates from the table numerarkusza and showing the results from tblGoraZleceniaNowaWyceny
Which means the records dont need to duplicate within that second table...


So it also is wrong
Click to expand...
I think it isnt the sql that is wrong but your interpertation of what it is supposed to do... or what you want it to do...

Not sure if this will work in access... but try:
Code: 
strSQL = " SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[nazwa] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[id_wycena_pre] " & _ 
         " FROM tblGoraZleceniaNowaWyceny " & _ 
         " WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza], [id_wycena_pre]) In ( " & _ 
                    " SELECT [numerarkusza], [id_wycena_pre] " & _ 
                    " FROM   [tblGoraZleceniaNowaWyceny] As Tmp " & _ 
                    " WHERE  [id_wycena_pre]=" & Forms!frmWycenyObszarroboczy!ID_wycena_pre & _
            	    " GROUP BY [numerarkusza] " & _ 
                    " HAVING Count(*)>1 )))"
If that also does not work for you, you will have to give a sample of what you want preferably in a database, no need for the entire thing, just have the two tables and the fields required....
Also be sure to include what you expect the outcome to be.
 
namliam said:
Why would it be wrong? You are getting duplicates from the table numerarkusza and showing the results from tblGoraZleceniaNowaWyceny
Which means the records dont need to duplicate within that second table...



I think it isnt the sql that is wrong but your interpertation of what it is supposed to do... or what you want it to do...

Not sure if this will work in access... but try:
Code: 
strSQL = " SELECT [tblGoraZleceniaNowaWyceny].[NumerArkusza] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[nazwa] " & _ 
              " , [tblGoraZleceniaNowaWyceny].[id_wycena_pre] " & _ 
         " FROM tblGoraZleceniaNowaWyceny " & _ 
         " WHERE ((([tblGoraZleceniaNowaWyceny].[numerarkusza], [id_wycena_pre]) In ( " & _ 
                    " SELECT [numerarkusza], [id_wycena_pre] " & _ 
                    " FROM   [tblGoraZleceniaNowaWyceny] As Tmp " & _ 
                    " WHERE  [id_wycena_pre]=" & Forms!frmWycenyObszarroboczy!ID_wycena_pre & _
            	    " GROUP BY [numerarkusza] " & _ 
                    " HAVING Count(*)>1 )))"
If that also does not work for you, you will have to give a sample of what you want preferably in a database, no need for the entire thing, just have the two tables and the fields required....
Also be sure to include what you expect the outcome to be.
Click to expand...

Ok so
1) Please Open SampleOk, then open form "frmWycenyObszarRoboczy" then open query "Query2" - see results? Please navigate to record number 2 and refresh "Query2".

That's what I need to achieve in one string to work with Recordset.

2) Open WRONGSample, then open form "frmWycenyObszarRoboczy" then click buttons, do the same with second record.

BTW
I edited the my first post, so you will can understand me better
 

Attachments

Easiest solution is to "simply" replace the query name by the sql...
Code: 
SELECT [Query1].[NumerArkusza], [Query1].[nazwa], [Query1].[id_wycena_pre]
FROM [U]Query1[/U]
WHERE ((([Query1].[numerarkusza]) In (SELECT [numerarkusza] FROM [[U]Query1[/U]] As Tmp 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 )));
to
Code: 
SELECT Query1.NumerArkusza, Query1.nazwa, Query1.id_wycena_pre
FROM [u]( 
     SELECT *
     FROM tblGoraZleceniaNowaWyceny
     WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre ) as Query1[/u]
WHERE (((Query1.NumerArkusza) In (
     SELECT [numerarkusza] FROM (
[U]          SELECT [numerarkusza] 
          FROM tblGoraZleceniaNowaWyceny
          WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre[/U]) As Tmp 
GROUP BY [numerarkusza] 
HAVING Count(*)>1 )));
 
Last edited:
The query as given:
Code: 
SELECT Query1.NumerArkusza, Query1.nazwa, Query1.id_wycena_pre
FROM ( 
     SELECT *
     FROM tblGoraZleceniaNowaWyceny
     WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre ) as Query1
WHERE (((Query1.NumerArkusza) In (
     SELECT [numerarkusza] 
     FROM (
          SELECT [numerarkusza] 
          FROM tblGoraZleceniaNowaWyceny
          WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre) As Tmp 
     GROUP BY [numerarkusza] 
     HAVING Count(*)>1 )));
WILL work and you can use that for your recordset source without any problem....

If you want to simplify it a little you can do it like this:
Code: 
SELECT tblGoraZleceniaNowaWyceny.NumerArkusza, tblGoraZleceniaNowaWyceny.nazwa, tblGoraZleceniaNowaWyceny.id_wycena_pre
FROM tblGoraZleceniaNowaWyceny
WHERE (((tblGoraZleceniaNowaWyceny.NumerArkusza) In (
     SELECT [numerarkusza] 
     FROM (
          SELECT [numerarkusza] 
          FROM tblGoraZleceniaNowaWyceny
          WHERE [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre) As Tmp 
          GROUP BY [numerarkusza] 
          HAVING Count(*)>1 )))
  AND [id_wycena_pre]=Forms!frmWycenyObszarroboczy!ID_wycena_pre
 
You must log in or register to reply here.

Similar threads

A
Run SQL based on SQL result
Replies
2
Views
471
arnelgp
arnelgp
M
Refresh Listbox Query
Replies
6
Views
989
monika_V
M
W
Combined Queries
Replies
15
Views
1,449
Ken Sheridan
Ken Sheridan
T
ListBox Doesn't Show Selected Values
Replies
10
Views
462
MajP
MajP
C
Matching vba recordset to form recordset
Replies
12
Views
708
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom