473,320 Members | 1,872 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,320 software developers and data experts.

SQL joins and parameter queries

23
I have a parameter query, the parameter it asks for being weekno:
Query 1:
Expand|Select|Wrap|Line Numbers
  1. SELECT count(*) as ct, weekno
  2. from weeknos
  3. where weeknoraised <= [weekno]
  4.   and weeknoupdated >  [weekno]
  5.   and status='completed' 
This works correctly, but I would like a new query result that lists all the weeknos and the result of Query1, so the weekno would supply the parameter to Query1.

I can use a lookup table to get a list of week numbers, but I can't work out how to link the two and get the correct result, which should be:

Expand|Select|Wrap|Line Numbers
  1. weekno    ct
  2. 1         0
  3. 2         2
  4. 3         1
Sep 3 '09 #1
9 2843
FishVal
2,653 Expert 2GB
As soon as [weekno] is the query parameter, [ct] column is expected to have non-zero value for not more then one week number.
Am I right? If so, then why do you ever want this?

Kind regards,
Fish.
Sep 3 '09 #2
charli
23
well currently people use the parameter query, every week they type the weeknumber in and append it to an excel spreadsheet. I was hoping to cut some stuff out by making a query/report that listed the weeknos and ct (which is a number of outstanding orders). Query1 is the query we already use to count oustanding orders, but I'm unsure how to incorporate it so that I can get an ongoing list of weeknos and the relevant [ct]
Sep 3 '09 #3
FishVal
2,653 Expert 2GB
Well. You could do it via aggregating query (see Access help concerning GROUP BY clause) outerjoined with table holding list of weekno's.
Sep 3 '09 #4
charli
23
With Query1 being the structure it is, I can't work out how to put a group by on it. It needs a specific week number to work on, rather than creating results that can be aggregated.

Table:
status
dateraised
dateupdated

Query1 takes a weeknumber, and counts the number of records that were raised before that week number, but updated (ie: completed) after that week number (which is then the number of outstanding orders). There is no field i can group by that makes it make sense
Sep 3 '09 #5
FishVal
2,653 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. SELECT * from 
  2. tblWeekNosList LEFT JOIN  weeknos 
  3. ON weeknos.weeknoraised <= tblWeekNosList.[weekno] and weeknos.weeknoupdated >  tblWeekNosList.[weekno] 
  4. WHERE weeknos.status='completed' 
CO: tblWeekNosList is a table containing list of week numbers.
Sep 3 '09 #6
NeoPa
32,556 Expert Mod 16PB
Charli,

I think you're going to (need to) pay a bit more attention when asking questions. What little information you've supplied actually contradicts info from another post.

Do you think it's fair to expect others to take extra time to save you the bother of thinking about the question in the first place?

I looked at what was here and after going around in circles a couple of times, where your information contradicts itself, I gave up in frustration. I wasn't amused.
Sep 3 '09 #7
charli
23
The two days I spent trying to work out how to ask this obviously didn't work, think I just confused myself more, sorry.

FishVals idea worked a treat, here is the complete SQL I ended up using to display a weekno and a count column (mondays being the table week numbers):
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(weeknos.weekraised) as count,
  2.        mondays.weekno
  3. FROM weeknos RIGHT JOIN mondays
  4.   ON weeknos.weeknoraised <= mondays.weekno
  5.  AND weeknos.weeknoupdated > mondays.weekno
  6. WHERE weeknos.status = 'completed'
  7. GROUP BY mondays.weekno
  8. ORDER BY mondays.weekno
Sep 4 '09 #8
FishVal
2,653 Expert 2GB
You are welcome .
Sep 4 '09 #9
NeoPa
32,556 Expert Mod 16PB
@charli
No worries. I'm not here to bust anyone's balls if they try but fail. If you made the attempt then we're all good with that.

A little tip for posting SQL though :
It's (almost) always best to split out the different clauses (SELECT; FROM; WHERE; etc) onto separate lines when posting. Reading SQL when you have to scroll it manually is much harder to understand and work with ;)

PS. I'm glad you got your solution and thank you for posting to say so.
Sep 4 '09 #10

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

Similar topics

6
by: jgalzic | last post by:
Hi, I'm having trouble doing joins correctly on two tables. I've read up a lot about the different types of joins and tried lots of variations on inner, outer, and left joins with no avail....
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
9
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with...
3
by: csomberg | last post by:
SQL Server 2000 Howdy All. Is it going to be faster to join several tables together and then select what I need from the set or is it more efficient to select only those columns I need in...
2
by: Pete | last post by:
After realizing the integrity of my data is questionable I went back to my update queries and opened up a few. The joins I had originally created between key fields were no longer there. It was...
7
by: Anony Mous | last post by:
Hi, I'm fairly new to this database, and have read much discussion on sub-queries. I've seen that they can be great for some queries, and downright slow for others. I have a table with two...
3
by: gwise | last post by:
I've got a database with a star schema and I'm kind of confused about what the optimizer is doing with my queries. I've got a fact table (about 24 M rows), which I'm joining to 3 dimensions. Each...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
36
by: TC | last post by:
I've used Access for many years. Several times, I've encountered a bug which I refer to as the "Vanishing Joins" bug. When it happens, joins vanish randomly from queries. More specifically, all...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.