Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TQuery help

Posted on 2007-03-31
11
Medium Priority
?
180 Views
Last Modified: 2013-11-23
I was wondering if anybody could help me with the following:

I have an Access database with tthe following fields:
ID
Date
Time
Album
Artist
Song
MP3
MPG
Filename
Credit
Now what I want is when the person open the form, the following must be displayed in labels according filtered in FromDate to ToDate.

Total MP3
Total MPG
Total Credit MP3
Total Credit MPG
0
Comment
Question by:DigitalNam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828308
Hello DigitalNam,

  what is the value of Total MP3 / MPG ?
  also, what is the Total Credit MP3 / MPG ?
  I mean, how are they calculated?
  do you want count(), sum()  and what is the rule to count() / sum() the mp3/mpg ...

Regards,

angelIII
0
 
LVL 6

Expert Comment

by:bokist
ID: 18828319
Hi !

It is not clear, how to find where the credit belongs(mp3 or mpg).
What is the key field for totals(ID ?).

regards,
  steve

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828321
Angelll:

When the user plays a mp3 for example, the album song name etc is added to the db with the value of the credit for a mp3 or mpg. So what I want is to give the user a summary of the total credits where field is MP3 of total credits where field is MPG for a certain period.
This is how my db looks at the moment.

ID      Date      Time      Album      Artist      Song      MP3      MPG      FileName      Credit
294      28/03/2007      22:42:02      Anastacia      Anastacia      Welcome to My Truth      Yes      No      07 Welcome to My Truth Anastacia Anastacia Rock 12      1
295      28/03/2007      22:42:13                        No      Yes      Metallica - The Unforgiven II.mpg      5
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828323
select sum(iif(MP3=Yes,1,0)) TotalMP3
,sum(iif(MPG=Yes,1,0)) TotalMPG
,sum(iif(MP3=Yes,Credit,0)) TotalCreditMP3
,sum(iif(MPG=Yes,Credit,0)) TotalCreditMPG
from yourtable
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828333
Angel gives me syntax error (missing operator) in sum(iif(MP3=Yes,1,0)) TotalMP3. How can I get the returned values to display in normal labels?
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828336
and how can we base the query on a certain time period? with a calendar dropdown?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828364
in case the field MP3/MPG are boolean, use True instead of yes.
if its a string field, use 'Yes' instead
possible also, you need to use ; instead of ,   that depends on some regional settings

>How can I get the returned values to display in normal labels?
labels can have a data source also.
ie, make the above query a view (querydef) etc...
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828386
Angel, got it wo work, like with the following

select sum(iif(MP3=True,1,0)) AS TotalMP3 , sum(iif(MPG=True,1,0)) AS TotalMPG , sum(iif(MP3=True,Credit,0)) AS TotalCreditMP3 ,sum(iif(MPG=True,Credit,0)) AS TotalCreditMPG from STATS

Seems like the AS was missing. Regarding the label question, I really don't understand your answer. I don't want to display the values returned by the query in a DBgrid, but all in labels.
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828397
Ok, I have managed to get the values to TLabel like this

Label22.Caption := 'Total for MPG: ' + IntToStr(DM.Query.Fields[1].AsInteger);

But now how do I get it to show from the 01/01/2007 to 03/01/2007?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 18828406
>But now how do I get it to show from the 01/01/2007 to 03/01/2007?

add that condition in the query:


select sum(iif(MP3=True,1,0)) AS TotalMP3 , sum(iif(MPG=True,1,0)) AS TotalMPG , sum(iif(MP3=True,Credit,0)) AS TotalCreditMP3 ,sum(iif(MPG=True,Credit,0)) AS TotalCreditMPG
from STATS
WHERE [Date] >= #2007/01/01#
AND [Date] < #2007/03/01#
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 18828420
Thanks for the help Angelll... Saved me heaps of time...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

610 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