Combined Queries

[weilerdo67]

Hello, I hope someone can point me in the right direction. I have a DB that has a Table "Submittals" that has my data. I need to be able to create a report from a query that shows me the number of submittals, the number due back and the number of responses by month. I am able to create separate queries for each and they work. But I cant seem to combine them into 1 query. I am sure its something I am missing in the grouping. Any help would be appreciated as i am stumped. Here are my individual queries. And how I am trying to get the output.

Qry 1:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Date1])) & "/ " & DatePart("yyyy",[Date1]) AS Submittal1
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Date1])) & "/ " & DatePart("yyyy",[Date1]);

Qry 2:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Actual])) & "/ " & DatePart("yyyy",[Actual]) AS Response
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Actual])) & "/ " & DatePart("yyyy",[Actual]);

Qry 3:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Due_Date])) & "/ " & DatePart("yyyy",[Due_Date]) AS Due
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Due_Date])) & "/ " & DatePart("yyyy",[Due_Date]);

Thanks in advance for looking at this.

 

[plog]

I would do a UNION that feeds a cross tab:

1. Make a simple query for each status. Be sure that all 3 queries have the same SELECT aliases (StatusDate, Status).

qrySubmittals:

SELECT  (DatePart("m",[Date1])) & "/ " & DatePart("yyyy",[Date1]) AS StatusDate, 'Submitted' AS Status
FROM Submittals
WHERE (Date1 Is Not Null) AND (CDRL=Yes)

2. UNION those 3 queries together. After you make the 3 queries in step 1 above, use this query to bring all the data together:

qryAllStatuses

SELECT StatusDate, Status FROM qrySubmittals
UNION ALL SELECT StatusDate, Status FROM qryDues
UNION ALL SELECT StatusDate, Status FROM qryResponses

3. Then you make a crosstab using qryAllStatuses as your base. You use the StatusDate field as row headings and the Status field as your column headings and you can also count on it as well.

 

[LarryE]

Would you please post a screenshot of your table and relationship design so we can at least see how you application works and what kinds of things you are attempting to track.

 

[weilerdo67]

That worked great, thank you for all you help. On to making it into a report.

 

[CJ_London]

Rather than using multiple functions, use the format function

Format(date1,”mm-yyyy”)

 

[weilerdo67]

Format(date1,mm-yyyy)

Thanks CJ, I have tried that but keep getting errors or it becomes an input IE when I tried the code below it gives me a pop up to input mm and then one for yyyy, I was trying to get it to show "Nov 2024" instead of 11-2024 to make it cleaner and so that it can sort by oldest to newest but cant seem to get it to work.

SELECT (Format(date1,mm yyyy)) AS StatusDate, 'Submitted' AS Status
FROM Submittals
WHERE (Date1 Is Not Null) AND (CDRL=Yes)

 

[Gasman]

The format needs to be enclosed with ""

Format(date1,"mm yyyy")

Nov 2024 will sort after Dec 2024 if you do that, and Dec 2024 before Mar 2024???

 

[weilerdo67]

The format needs to be enclosed with ""

Format(date1,"mm yyyy")

Nov 2024 will sort after Dec 2024 if you do that, and Dec 2024 before Mar 2024???

Thanks Gasman, I figured it was something simple I was missing. they are showing the month names now but as you pointed out they arent in order by months its Alphabetical LOL

 

[Gasman]

So you sort on month number and year and just show month and year.

Always pisses me off when I select my address and 10 comes before 8. Seriously, no-one tests anything nowadays, or just cannot be arsed.
I remember for our UK postcodes, B24 would be sorted before B4. ??? Still is.

 

[CJ_London]

My bad - but see you worked it out. To sort in date order, you would need to specify year before month e.g. yyyy-mm

You don’t need to display the sort column

 

[CJ_London]

. Seriously, no-one tests anything nowadays

One of my clients uses a bank which changed to a new system which apparently had been ‘extensively tested’ before being released. Nothing worked reliably from being able to log in to processing payments to downloading transactions.

Even the 100 page manual you get pointed to has errors such as referencing screens that look nothing like the reality.

A month on and there are still many issues and missing basic functionality. One of my favourites is lack of FAYT. You have to scroll through a list of around 800 beneficiaries to make a payment- the right one might be John smith, smith John, mr John smith, j smith, smith j

And then you have to verify you have the right smith, so you need to compare account numbers with what is on the invoice

And if you choose the wrong smith, you go right back to the beginning to select the required current account from which to make the payment.

What used to take a minute or so now can take 5 minutes

 

[weilerdo67]

My bad - but see you worked it out. To sort in date order, you would need to specify year before month e.g. yyyy-mm

You don’t need to display the sort column

Thank CJ & Gasman. I got it working, on the individual queries it was sorting correctly but between the union and crosstab it was getting messed up, so I added an expression field to the original 3 queries with CJ's format e.g. yyyy-mm so that the crosstab query would have a field to sort off. The report is now showing everything in the correct order.

 

[Gasman]

One of my clients uses a bank which changed to a new system which apparently had been ‘extensively tested’ before being released. Nothing worked reliably from being able to log in to processing payments to downloading transactions.

Even the 100 page manual you get pointed to has errors such as referencing screens that look nothing like the reality.

A month on and there are still many issues and missing basic functionality. One of my favourites is lack of FAYT. You have to scroll through a list of around 800 beneficiaries to make a payment- the right one might be John smith, smith John, mr John smith, j smith, smith j

And then you have to verify you have the right smith, so you need to compare account numbers with what is on the invoice

And if you choose the wrong smith, you go right back to the beginning to select the required current account from which to make the payment.

What used to take a minute or so now can take 5 minutes

I once worked as a call handler for a major bank in the UK. We were supporting businesses that wanted to go online, so quite a while ago.
The system checked the version of Netscape (yes that long ago) and if it was not, say 5.6, it would direct you to Netscape download page.

However, Netscape then updated their browser to 5.7.

So the customer would spend ages downloading and then installing the browser, (we were on dial up at that time), only to find when they tried to login again, they got the same message. After about 30 calls on this, I could see a pattern.
So I reported it. Oh, that will get fixed in the next version was the response. When will that be, I asked. 6 months down the line was the next response..

I went home that night and created a site and then had links for all this sort of stuff, like . . . . . . .
If you forgot your password, you had to go to a link (all the links were about 50 characters long, so many mistakes were made, meaning more calls) to download a form, complete it and fax it in. Then wait 3 or 4 hours.

With my site (12 characters), I would direct you the required link, and in there on one page was the lastest supported version of Netscape, and other links like password reset form, etc.

The company listened in on calls, and called me in to ask me what this site was for, so I explained. We were (5 of us) serving more customers faster (easier links) and getting better call handling time, due to my site, easy to get to and use. I even offered them the site.

Not their way, they said and sacked me.

 

[Pat Hartman]

so I added an expression field to the original 3 queries with CJ's format e.g. yyyy-mm so that the crosstab query would have a field to sort off. The report is now showing everything in the correct order.

NEVER format dates in queries UNLESS you intend to export the data and the receiving system needs the date formatted in a certain way. When you use the Format() function, you convert the date to a string and as you discovered, strings sort as strings NOT as dates. You can use the Format property of the control in a form or report to format the date.

Not their way, they said and sacked me.

Always distressing to get sacked but believe me, you were better off out of that environment.

 

[LarryE]

Hello, I hope someone can point me in the right direction. I have a DB that has a Table "Submittals" that has my data. I need to be able to create a report from a query that shows me the number of submittals, the number due back and the number of responses by month. I am able to create separate queries for each and they work. But I cant seem to combine them into 1 query. I am sure its something I am missing in the grouping. Any help would be appreciated as i am stumped. Here are my individual queries. And how I am trying to get the output.

Qry 1:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Date1])) & "/ " & DatePart("yyyy",[Date1]) AS Submittal1
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Date1])) & "/ " & DatePart("yyyy",[Date1]);

Qry 2:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Actual])) & "/ " & DatePart("yyyy",[Actual]) AS Response
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Actual])) & "/ " & DatePart("yyyy",[Actual]);

Qry 3:
SELECT Count(Submittals.CDRL) AS CountOfCDRL, (DatePart("m",[Due_Date])) & "/ " & DatePart("yyyy",[Due_Date]) AS Due
FROM Submittals
WHERE (((Submittals.CDRL)=Yes))
GROUP BY (DatePart("m",[Due_Date])) & "/ " & DatePart("yyyy",[Due_Date]);

View attachment 120533
Thanks in advance for looking at this.

It is very unusual to require 3 different queries just to populate a report. It appears you might be attempting to use ACCESS as you would EXCEL. Can you provide a screenshot of your table and relationship design so we can see how you constructed your project and provide a description of the kinds of events or things you are attempting to track?

Perhaps someone can help you design it so you wouldn't need 3 queries just to print a report.

 

[CJ_London]

NEVER format dates in queries UNLESS you intend to export the data and the receiving system needs the date formatted in a certain way

Or in this case where it is being used to sort a recordset but otherwise not used

 

[Pat Hartman]

Or in this case where it is being used to sort a recordset but otherwise not used

I thought my one exception covered all bases. Let me say it backwards. If you are not exporting the data to a different application that requires a specifically formatted date, NEVER use Format() to format a date in a query because you turn it into a string and from that point on, it is a string and not a date and therefore will not sort or compare as a date does. It will sort and compare as a string does.

If you insist on having a formatted date in a query, at least leave the unformatted date in the query and use that for sort and compare.

 

[Ken Sheridan]

I share LarryE's concerns about your table design. It should be possible to return different counts of entities where each count is determined by some attribute of each row in the source table. The following is an example of a simple query which returns the total number of transactions and the number of credit and debit transactions per month:

SELECT
    YEAR(TransactionDate) AS TransactionYear,
    MONTH(TransactionDate) AS TransactionMonth,
    COUNT(*) AS Transactions,
    SUM(IIF(Credit > 0, 1, 0)) AS Credits,
    SUM(IIF(Debit > 0, 1, 0)) AS Debits
FROM
    TransactionsCD
GROUP BY
    YEAR(TransactionDate),
    MONTH(TransactionDate);

The conditional aggregation is done by summing the return value of an IIF function call which returns 1 or 0. Consequently the value returned is the count of the rows which meet the criterion of the IIF function call.

In the report you can easily return the month/year in a single text box control with a ControlSource property of:

=MonthName(TransactionMonth) & " " & TransactionYear