By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,482 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,482 IT Pros & Developers. It's quick & easy.

too many sql statements

Tarantulus
100+
P: 114
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
Share this Question
Share on Google+
6 Replies

JConsulting
Expert 100+
P: 603
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
100+
P: 114
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
Expert 100+
P: 603
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
100+
P: 114
Unfortunately, this failed. it asked me to input the values for tblcategory and tblgroup
May 25 '07 #5

JConsulting
Expert 100+
P: 603
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
Expert Mod 15k+
P: 31,770
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

Post your reply

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