SQL Query + Percentage

I

If

Good evening,

I have the SQL Query below which gives me a total + Percentage result for
all my records.
The Name of my Table is "Datas".
I have two fields : "Customers" and "'Type".

This SQL query gives me a total result for all the customers.

Query Result :
Total Type1 Type2 % Type 1 % Type 2
100 15 85 15% 85%

I would like to obtain a total by customer.

Query Result :
Customer Total Type1 Type2 % Type 1 % Type 2
1 10 1 9 10% 90%
2 8 6 2 75% 25%
3 5 2 3 40% 60%
.....

The value in the Type field can be different, 1111111100 or 11551111100 for
example.


Thanks for your help.


--------------------------------------------------------------------------------
SELECT

DCount("[Type]","Datas") AS Total,
DCount("[Type]","Datas","[Type] like '*100*'") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*'") AS [Type 2],

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

DCount("[Type]","Datas"),
DCount("[Type]","Datas","[Type] like '*100*'"),
DCount("[Type]","Datas","[Type] like '*300*'"),

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent"),
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent");
--------------------------------------------------------------------------------
 
J

John Viescas

SELECT CustomerID,

DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]") AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID]")/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

CustomerID;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I

If

Thanks for your answer.
With this code I have Error Syntax 3075.


John Viescas said:
SELECT CustomerID,

DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]") AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID]")/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

CustomerID;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
If said:
Good evening,

I have the SQL Query below which gives me a total + Percentage result for
all my records.
The Name of my Table is "Datas".
I have two fields : "Customers" and "'Type".

This SQL query gives me a total result for all the customers.

Query Result :
Total Type1 Type2 % Type 1 % Type 2
100 15 85 15% 85%

I would like to obtain a total by customer.

Query Result :
Customer Total Type1 Type2 % Type 1 % Type 2
1 10 1 9 10% 90%
2 8 6 2 75% 25%
3 5 2 3 40% 60%
....

The value in the Type field can be different, 1111111100 or 11551111100
for example.


Thanks for your help.


--------------------------------------------------------------------------------
SELECT

DCount("[Type]","Datas") AS Total,
DCount("[Type]","Datas","[Type] like '*100*'") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*'") AS [Type 2],

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

DCount("[Type]","Datas"),
DCount("[Type]","Datas","[Type] like '*100*'"),
DCount("[Type]","Datas","[Type] like '*300*'"),

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent"),
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent");
Click to expand...
Click to expand...
 
J

John Viescas

Well, I'm just guessing that you have a field named CustomerID in the Datas
table. I think I also left in some stray quotes. If you do have a
CustomerID field, then try this:

SELECT CustomerID,
DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]) AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]) AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],
FROM Datas
GROUP BY
CustomerID;

If you don't have a CustomerID field, you'll need to modify the SQL to
accomodate whatever field is unique for customers.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
If said:
Thanks for your answer.
With this code I have Error Syntax 3075.


John Viescas said:
SELECT CustomerID,

DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]") AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID]")/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

CustomerID;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
If said:
Good evening,

I have the SQL Query below which gives me a total + Percentage result
for all my records.
The Name of my Table is "Datas".
I have two fields : "Customers" and "'Type".

This SQL query gives me a total result for all the customers.

Query Result :
Total Type1 Type2 % Type 1 % Type 2
100 15 85 15% 85%

I would like to obtain a total by customer.

Query Result :
Customer Total Type1 Type2 % Type 1 % Type 2
1 10 1 9 10% 90%
2 8 6 2 75% 25%
3 5 2 3 40% 60%
....

The value in the Type field can be different, 1111111100 or 11551111100
for example.


Thanks for your help.


--------------------------------------------------------------------------------
SELECT

DCount("[Type]","Datas") AS Total,
DCount("[Type]","Datas","[Type] like '*100*'") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*'") AS [Type 2],

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

DCount("[Type]","Datas"),
DCount("[Type]","Datas","[Type] like '*100*'"),
DCount("[Type]","Datas","[Type] like '*300*'"),

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent"),
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent");
Click to expand...
Click to expand...
Click to expand...
 
I

If

Great Thanks to you.
Yves


John Viescas said:
Well, I'm just guessing that you have a field named CustomerID in the
Datas table. I think I also left in some stray quotes. If you do have a
CustomerID field, then try this:

SELECT CustomerID,
DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]) AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]) AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],
FROM Datas
GROUP BY
CustomerID;

If you don't have a CustomerID field, you'll need to modify the SQL to
accomodate whatever field is unique for customers.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
If said:
Thanks for your answer.
With this code I have Error Syntax 3075.


John Viescas said:
SELECT CustomerID,

DCount("[Type]","Datas", "CustomerID = " & [CustomerID]) AS Total,
DCount("[Type]","Datas","[Type] like '*100*' AND CustomerID = " &
[CustomerID]") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*' AND CustomerID = " &
[CustomerID]") AS [Type 2],
Format(DCount("[Type]","Datas","[Type] like
'*100*' AND CustomerID = " & [CustomerID]")/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]), "Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*' AND CustomerID = " & [CustomerID])/DCount("[Type]","Datas",
"CustomerID = " & [CustomerID]),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

CustomerID;

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Good evening,

I have the SQL Query below which gives me a total + Percentage result
for all my records.
The Name of my Table is "Datas".
I have two fields : "Customers" and "'Type".

This SQL query gives me a total result for all the customers.

Query Result :
Total Type1 Type2 % Type 1 % Type 2
100 15 85 15% 85%

I would like to obtain a total by customer.

Query Result :
Customer Total Type1 Type2 % Type 1 % Type 2
1 10 1 9 10% 90%
2 8 6 2 75% 25%
3 5 2 3 40% 60%
....

The value in the Type field can be different, 1111111100 or 11551111100
for example.


Thanks for your help.


--------------------------------------------------------------------------------
SELECT

DCount("[Type]","Datas") AS Total,
DCount("[Type]","Datas","[Type] like '*100*'") AS [Type 1],
DCount("[Type]","Datas","[Type] like '*300*'") AS [Type 2],

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 1],
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent") AS [% Type 2],

FROM Datas

GROUP BY

DCount("[Type]","Datas"),
DCount("[Type]","Datas","[Type] like '*100*'"),
DCount("[Type]","Datas","[Type] like '*300*'"),

Format(DCount("[Type]","Datas","[Type] like
'*100*'")/DCount("[Type]","Datas"),"Percent"),
Format(DCount("[Type]","Datas","[Type] like
'*300*'")/DCount("[Type]","Datas"),"Percent");
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format % 3
Query with Percent 1
Counting records in a query 10
SQL within DCount() 5
Dcount revisted 4
Query Formula need HELP 3
Count query help 3
Multiple Unrelated Tables w/Similar Field in Single Query 5

Top