Solved

Please help me with the query

Posted on 2011-03-09
6
264 Views
Last Modified: 2012-08-13
let say you run the query
Select * from tblMain

if you have the data you will get result:
num   name     zip
1      Steven 91356
2     Leonad 94875
and so on...
how about if you do not have anything you will get this
num   name     zip
how to modify the query and get just something like this:
0
0
Comment
Question by:rfedorov
  • 3
  • 2
6 Comments
 
LVL 9
ID: 35089458
That's not how queries work.  They return records if they exist, not if they don't.  Can you tell us what you are trying to accomplish with this "zero" record?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35089494
Try this.
Select num,name,zip from tblMain
union all
select 0,null,null where not exists (select 1 from tblMain)

Open in new window

0
 

Author Comment

by:rfedorov
ID: 35095788
Thank you, guys.
To ArmenStein:i need to get the count of records even if there is no records. ---in this case is Zero is my result. I need the count to add to one more query results to get the final number
To: Sharath_123: i am getting the error message
Syntax error(missing operator) in query exression 'null where not exists (select 1 from tblMain)
'
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 40

Expert Comment

by:Sharath
ID: 35102035
Check this
Select num,name,zip from tblMain
union 
select 0,null,null from tblMain where not exists (select 1 from tblMain)

Open in new window

0
 

Author Comment

by:rfedorov
ID: 35109940
thank you, it did not give me the error, but it does not give me the zero
i added the extra field Date
what i want, i think it make more sense, show me * from the table where date is not todays

and it stll gives me just a header:
num name zip Date

and i want like that
num name zip Date
0
is this possible???
Select num,name,zip,Date from tblMain where Date =format(now,"mm/dd/yyyy")
union 
select 0,null,null,null from tblMain where not exists (select 1 from tblMain)

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35113342
You have to add the WHERE condition in the sub query also.
Select num,name,zip,Date from tblMain where Date =format(now,"mm/dd/yyyy")
union 
select 0,null,null,null from tblMain where not exists (select 1 from tblMain where Date =format(now,"mm/dd/yyyy"))

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now