Query 1 Field from specific lines.

[Guest]

I would like to create a query that will return 2 fields, based on specific
criteria.
The info is currently in the same field, Total. Each line is a different
total. Im not sure how to explain.

This is an example of the data base i am pulling from in datasheet view

COMPANY TYPE TOTAL
A 1 1000
A 2 2000
A 3 3000
B 1 1000
B 2 2000
B 3 3000

I would like to have a query return what is below

COMPANY TYPE 1 TYPE 2
A 1000 2000
B 1000 2000

In the data base there many "types" (1,2,3,4,5,6,etc) for each company.
I only want to return 2 of the Types as shown above.

 

[Guest]

Create a crosstab query using the wizard.
Select your table.
Select Company as Row.
Select Type as Column.
Select Total as Value.

Open in design view. Delete the Totals column it creates as a row if you do
not want it. Add criteria to select the types you want.

 

[Tom Ellison]

Dear DC:

How about this:

SELECT Company, SUM(T1) AS Type1, Sum(T2) AS Type2
FROM (
SELECT Company, Total AS T1, 0 AS T2
FROM CTT
WHERE Type = 1
UNION ALL
SELECT Company, 0 AS T1, Total AS T2
FROM CTT
WHERE Type = 2
)
GROUP BY Company

Replace CTT with the actual name of your table in the above. Initially,
please do not change anything else, till you see if it runs.

Does this work for you?

Tom Ellison