473,289 Members | 1,729 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

too many sql statements

Tarantulus
114 100+
Hi Guys,

This is my first post, so I'm sorry If I do something wrong!

Ok, here is my problem.

I have a database with two tables we'll call them tblcategory and tblgroup
now each entry in tblgroup is assigned to one of the entries in tbcategory thus:

tbgroup tblcategory
1 1
2 1
3 1
4 2
5 2

I have another table which pulls the data from both of these and looks very similar to the above (only difference is a date field, and an EntryID field) we'll call this tblwhole

what I want to do, is run a query like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT EntryID
  2. FROM tblwhole
  3. WHERE (tblgroup=1) AND (tblcategory=1) AND (DATE='userinput')
for each of the categories, obviously I don't want to write a single query for each category (e.g category=1 group=1, category=1 group=2 etc etc) so I'm at a loss how to do it!

any help would be appreciated

thanks in advance
May 24 '07 #1
6 1433
JConsulting
603 Expert 512MB
Hi Guys,

This is my first post, so I'm sorry If I do something wrong!

Ok, here is my problem.

I have a database with two tables we'll call them tblcategory and tblgroup
now each entry in tblgroup is assigned to one of the entries in tbcategory thus:

tbgroup tblcategory
1 1
2 1
3 1
4 2
5 2

I have another table which pulls the data from both of these and looks very similar to the above (only difference is a date field, and an EntryID field) we'll call this tblwhole

what I want to do, is run a query like this:

SELECT COUNT EntryID FROM tblwhole WHERE (tblgroup=1) AND (tblcategory=1) AND (DATE='userinput')

for each of the categories, obviously I don't want to write a single query for each category (e.g category=1 group=1, category=1 group=2 etc etc) so I'm at a loss how to do it!

any help would be appreciated

thanks in advance

so..you want to open up x number of select queries at the same time?

Or do you want them all put somewhere like in a table instead...so you can view them all at once?

Also..since your query has a user input, you probably want to make a form with a textbox that they can enter the date once.

Let us know the objective..so we can help.
J
May 24 '07 #2
Tarantulus
114 100+
Sorry, I should have been more specific:

I want the final report to read:

between date A and date B

category 1, group one has X number of entries
category 2, group two has Y number of entries

and so on and so on..

I would prefer it to output to a form, but a temporary table would do.

hope this makes more sense now
May 24 '07 #3
JConsulting
603 Expert 512MB
Sorry, I should have been more specific:

I want the final report to read:

between date A and date B

category 1, group one has X number of entries
category 2, group two has Y number of entries

and so on and so on..

I would prefer it to output to a form, but a temporary table would do.

hope this makes more sense now

try this

Expand|Select|Wrap|Line Numbers
  1. select tblGroup,
  2.        tblCategory,
  3.        Count([entryID])
  4. from tblWhole
  5. where [yourdatefield] between [Enter Start Date] and [Enter End Date]
  6. group by tblGroup, tblCategory;
May 25 '07 #4
Tarantulus
114 100+
Unfortunately, this failed. it asked me to input the values for tblcategory and tblgroup
May 25 '07 #5
JConsulting
603 Expert 512MB
Unfortunately, this failed. it asked me to input the values for tblcategory and tblgroup
what are the actual field names? you'll need to plug those in in place of the tblCategory and tblGroup. Sorry...didn't realise there would be confustion. Let me know if you run into problems.
May 25 '07 #6
NeoPa
32,554 Expert Mod 16PB
Unfortunately, this failed. it asked me to input the values for tblcategory and tblgroup
Perhaps it would be a good idea if you posted the MetaData for all (3 of) your tables here. Please use the format in this example :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
May 25 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Bart Nessux | last post by:
Should an if statement have a corresponding else statement? Or, is it OK to have an if statement by itself. For completeness, it seems the two should be together, but from experience I know that a...
9
by: Jaime Wyant | last post by:
I know I've seen this somewhere, but can't seem to google it. Is there a way to use an alternate statement separator, other than the default ';'? jw
1
by: Tom D | last post by:
I'm rewriting a database interface that our company currently has. Currently it's using the Pear::DB interface, but we found that that was introducing a bit too much overhead. I'm rewriting the...
39
by: slogging_away | last post by:
Hi - I'm running Python 2.4.2 (#67, Sep 28 2005, 12:41:11) on win32, and have a script that makes numerous checks on text files, (configuration files), so discrepancies can be reported. The script...
0
by: Fuzzyman | last post by:
Hello all, The following is a copy of a blog entry. It's asking a question about future statements and the built in compile function. I'd appreciate any pointers or comments about possible...
20
by: Neroku | last post by:
Hello, i would like to know what the serious definition of statements and expressions is: i know an expression are evaluated to a value, i.e: 1 == 2 5+7 foo( 1,2) and a statement is...
2
by: ojorus | last post by:
Hi! Some questions regarding the mysqli-extension (php5) 1) Prepared statements: If I understand things right, prepared statements will give better performance if you make several similar...
3
by: Dmitri | last post by:
Hello! I have a developer that is playing around with some SQL statements using VB.NET. He has a test table in a SQL 2000 database, and he has about 2000 generated INSERT statements. When the...
0
by: Gary Herron | last post by:
Ohad Frand wrote: There is no way you can consider 'elif', 'else', 'except', and 'from' statements. However, as someone pointed out, the kwlist from the keyword module is the closest thing we...
0
by: Ohad Frand | last post by:
Hi Thanks a lot for your reply I think the main uses for it is to study the language and to see that I didn't miss anything else or that something is changed from one version to another. The...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.