querry nightmare

[thadson]

Hi,

I'm having a very hard time to figure this ridle out and maybe there is no solution, or I'm just going in the wrong direction, however here it is:

I have to calculate the number of deals that come in every day, for that week, month, and year. Than some deals are rejected and I have to do the same thing. The query below shows what I did. So far it works...

However I would need to calculate the true percentage (%) of the rejected deals to the Imported deals in all 4 respects (daily, weekly, monthly and yearly.) showing the value with 2 decimals %-age as the next row in this union querry. Anything I tried so far is a total disaster.

The Table is Customers
The Fields are ImpDate (import date)
PkgID (the 1st 3 character tells me the room I got the deal from)
RejReason (this field containes the preset reject reasons)

Here is my initial querry:

SELECT 1 AS Sort, "Imported Deals" AS Details, Sum(IIf(Format([Customers]![ImpDate],"dd")=Format([Enter Date],"dd"),1,0)) AS Daily, Sum(IIf(Format([Customers]![ImpDate],"ww")=Format([Enter Date],"ww"),1,0)) AS Weekly, Sum(IIf(Format([Customers]![ImpDate],"mmyy")=Format([Enter Date],"mmyy"),1,0)) AS Monthly, Sum(IIf(Format([Customers]![ImpDate],"yyyy")=Format([Enter Date],"yyyy"),1,0)) AS Yearly
FROM Customers
WHERE (((Left([Customers]![PkgID],3)) Like "*ST1*"))
GROUP BY 1;

UNION

SELECT 2 AS Sort, "Rejected Deals" AS Details, Sum(IIf(Format([Customers]![ImpDate],"dd")=Format([Enter Date],"dd"),1,0)) AS Daily, Sum(IIf(Format([Customers]![ImpDate],"ww")=Format([Enter Date],"ww"),1,0)) AS Weekly, Sum(IIf(Format([Customers]![ImpDate],"mmyy")=Format([Enter Date],"mmyy"),1,0)) AS Monthly, Sum(IIf(Format([Customers]![ImpDate],"yyyy")=Format([Enter Date],"yyyy"),1,0)) AS Yearly
FROM Customers
WHERE (((Left([Customers]![PkgID],3)) Like "*ST1*") AND ((Customers.RejReason) Is Not Null))
GROUP BY 2;

Please be patient with me, I'm very new to this.

Thanks.

 

[cogent1]

What happens when you run this?

 

[thadson]

it looks like this

I would get a table like this:

Details: Daily Weekly Monthly Yearly
Imported Deals: 100 434 2312 18352
Rejected Deals: 12 48 233 3118

Where the weekly, monthly and yearly deals get calculated to the day that was entered at [Enter Date].

the next line should be

Reject %: 12.00 11.05 10.07 16.98

Is this possible to do?
Or is there any other way?

I have a scaled down test database about 1Mb that I could send to someone who could help. Thanks.

 

[cogent1]

Ok Send it to me

 

[thadson]

Hi cogent1,

Have you been able to get the files?
Any ideas?

Attila

 

[cogent1]

yes, received. I haven't had time to work on it yet, but will be back to you within 24 hours

 

[Jon K]

You can save your query result into a table tblDetails. Change its numeric fields into single or double and set their formats as Standard.

Then you can run this code from the click event of a form to add the percentages:
------------------------------------
Private Sub Command0_Click()
Dim db As Database
Dim rs As Recordset
Dim ImpDaily, ImpWeekly, ImpMonthly, ImpYearly
Dim RejDaily, RejWeekly, RejMonthly, RejYearly

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDetails")

ImpDaily = rs![Daily]
ImpWeekly = rs![Weekly]
ImpMonthly = rs![Monthly]
ImpYearly = rs![Yearly]

rs.MoveNext
RejDaily = rs![Daily]
RejWeekly = rs![Weekly]
RejMonthly = rs![Monthly]
RejYearly = rs![Yearly]

rs.AddNew
rs![Details:] = "Reject %:"
rs!Daily = RejDaily / ImpDaily * 100
rs!Weekly = RejWeekly / ImpWeekly * 100
rs!Monthly = RejMonthly / ImpMonthly * 100
rs!Yearly = RejYearly / ImpYearly * 100
rs.Update

End Sub
-------------------------------------

You cannot have numbers in integers and percentages in two places of decimal in the same column in an Access table. When you set the field format to Standard, all the figures in the column will be in two places of decimal.

If an import figure happens to be zero, you must amend the above code to take care of "division by zero".


I think it will be much simpler and easier if you export the query result to Excel and add the percentages and formats there.

---------------------------------------------------------
Sorry for a typing mistake.

The code should be run from the click event of a COMMAND BUTTON on the form.

In the code, "Command0" is the name of the command button.

 

[thadson]

union querry into table?

I could not find a way to make the union querry into a table. How is that done?

 

[Jon K]

You can create a make-table query based on your union query.

Suppose you have saved your union query as qryOne.

Now click New on the Queries tab. Switch to SQL View and type this:

SELECT * INTO tblDetails
FROM qryOne


A make-table query uses a SELECT ... INTO statement.
When you run this query, a table tblDetails will be created.

 

[Jon K]

Sorry for a typing mistake in my first post. The sentence:

"Then you can run this code from the click event of a form to add the percentages"

should read as:

"Then you can run this code from the click event of a COMMAND BUTTON ON THE form to add the percentages"


In the code, "Command0" is the name of the command button.

 

[thadson]

form

Hi Jon K,

I did everything as you described.

When I click the button command0 I get the following error message:

"Microsoft Visual Basic
Compile error:
User-defined type not defined"

with "db as database" marked blue on the second line in the code.

Thadson

 

[Jon K]

I think you are using Access 2000. I am sorry I haven't made it clear I use Access 97.

Access 97 defaults to using DAO, while Access 2000 defaults to using ADO.

In Access 2000, select the form. Click the Code button on the toolbar. From the Tools menu, select References... There should be a Microsoft DAO 3.6 Object Library in the "Available References:" list. Check the box in front of it. When it is highlighted, use the Priority button to move this DAO Object Library up until it is above the Microsoft ActiveX Data Objects 2.x Library. Click OK.

The DAO reference is now added for this database. The code should run successfully.

 

[thadson]

Jon K,

Thanks. I did the above, but I still got error messages.

As I said earlier I'm a newbie. Please bare with me.

Is there any way i could have a "private" with you?
I'm usually here between 9pm till 6am EST.

However if not, here is the error I got now:

Runtime error 13
Type Mismatch

and in debug, the line
Set rs = db.OpenRecordset("tblDetails")
is yellow...

It seems I also missed something. You said: "You can save your query result into a table tblDetails. Change its numeric fields into single or double and set their formats as Standard."

I did save the results in table tblDetails however I can't see anything that would let me change the numeric fields or change their format to standard.

 

[Jon K]

You can change the field type and field format of the table in the table Design View.

If you still cannot fix it, you can send me your .mdb file to
dl6391@myrealbox.com

I'll see if there is any thing I can do.

 

[thadson]

Hi and thanks again.

There is still an error so I sent an e-mail to you.