SQL joins and parameter queries

I have a parameter query, the parameter it asks for being weekno:
Query 1:
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:

re: SQL joins and parameter queries

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.

re: SQL joins and parameter queries

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]

re: SQL joins and parameter queries

Well. You could do it via aggregating query (see Access help concerning GROUP BY clause) outerjoined with table holding list of weekno's.

re: SQL joins and parameter queries

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

re: SQL joins and parameter queries

CO: tblWeekNosList is a table containing list of week numbers.

re: SQL joins and parameter queries

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.

re: SQL joins and parameter queries

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):

re: SQL joins and parameter queries

You are welcome .

re: SQL joins and parameter queries

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.