DigitalNam
asked on
TQuery help
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
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
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
It is not clear, how to find where the credit belongs(mp3 or mpg).
What is the key field for totals(ID ?).
regards,
steve
ASKER
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
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
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
,sum(iif(MPG=Yes,1,0)) TotalMPG
,sum(iif(MP3=Yes,Credit,0)
,sum(iif(MPG=Yes,Credit,0)
from yourtable
ASKER
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?
ASKER
and how can we base the query on a certain time period? with a calendar dropdown?
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...
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...
ASKER
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.
select sum(iif(MP3=True,1,0)) AS TotalMP3 , sum(iif(MPG=True,1,0)) AS TotalMPG , sum(iif(MP3=True,Credit,0)
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.
ASKER
Ok, I have managed to get the values to TLabel like this
Label22.Caption := 'Total for MPG: ' + IntToStr(DM.Query.Fields[1 ].AsIntege r);
But now how do I get it to show from the 01/01/2007 to 03/01/2007?
Label22.Caption := 'Total for MPG: ' + IntToStr(DM.Query.Fields[1
But now how do I get it to show from the 01/01/2007 to 03/01/2007?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help Angelll... Saved me heaps of time...
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