Query Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have been trying to figure this out for hours and now I'm sure if it is at
all possible. I have a query that is a run from another query. It lists:

Invoicedate,Invoicenumber,Company,Liters,Alcoholper

For my end result, I need to be able to create a report that lists
Company, InvoiceDate, InvoiceNum,Liters by Alcholper >14%, Liters by
Alcholper <14%

I can separate the alcoholper with different queries but I cannot join them
together again by invoice number in order to create the report.

Any recommendations would be extremely helpful.

thanks,

mel
 
Try:

Select InvoiceDate, Invoicenumber, Company, Sum(IIF([Alcoholper]>0.14,
[Liters],0)) as [LitersGreater14pct], Sum(IIF([Alcoholper]<0.14,
[Liters],0)) as [LitersLess14pct] From TableName Group by InvoiceDate,
Invoicenumber, Company;
 
Hi Mel,

In your query, create a new field, something like alcpercent. It will
have an expression like this:

alcpercent:iif(Alcholper > 14%,1,0).

You can use this new field in your report as part of the sorting and
grouping criteria.

Hope this helps.
 
You guys are the best!!!!

Thank you soooo much!!

TPratt said:
Try:

Select InvoiceDate, Invoicenumber, Company, Sum(IIF([Alcoholper]>0.14,
[Liters],0)) as [LitersGreater14pct], Sum(IIF([Alcoholper]<0.14,
[Liters],0)) as [LitersLess14pct] From TableName Group by InvoiceDate,
Invoicenumber, Company;



Mel said:
Hello,

I have been trying to figure this out for hours and now I'm sure if it is at
all possible. I have a query that is a run from another query. It lists:

Invoicedate,Invoicenumber,Company,Liters,Alcoholper

For my end result, I need to be able to create a report that lists
Company, InvoiceDate, InvoiceNum,Liters by Alcholper >14%, Liters by
Alcholper <14%

I can separate the alcoholper with different queries but I cannot join them
together again by invoice number in order to create the report.

Any recommendations would be extremely helpful.

thanks,

mel
Click to expand...
Click to expand...
 
Pleas help me to creat this quires by using the Northwind Access 2003
Database to create your SQL queries for each question.
i have tried for 4 hours ,but was so difficult to me
i realy need help with these questions

2 Display the supplier name, product name, and unit price for all products.

3.Display number of employees born in or after 1960.

4.Display all the orders taken between November 1, 1994 and January 31, 1995.

5.Display the average price for products in the 'Seafood' category.

6.Display the number of orders. (Hint: Use count.)

7.Display all suppliers that have a 'K' somewhere in their company name.

8.Display the customer name, employee name, and employee ID for all orders
that were handled by the Sales Representatives.

9.Display the order number and order total cost for each order placed.

10.Display the supplier name and the supplier contact name for all suppliers
that provide condiments.

11.Display the category IDs in numerical order. Secondly, sort on product
name in reverse order (descending).

12.Display all of the countries of all the customers with no duplications.
(Hint: Use DISTINCT)

13.Determine the average quantity of a dairy product item.

14.Display all the orders that received a discount of 10% or larger on at
least one product in the order.

15.Display all of the employees that live in the United States.

think you






i love juri


"
 
yes it is homework
i have treid many time ,but idid not know how to do it
pleas help me
 
I have no problem to answer to a given problem, but that does not extend to
make homework. Those queries seem rather simple, to me, as example, what
have you tried to solve:

3.Display number of employees born in or after 1960.


You have Northwind.mdb on your computer, right? If not, that is indeed an
impossible problem to solve.

And Access?

If so, open Northwind. In the table, you are likely to note there is a table
Employees, Open it in data view, and you should spot a field called Birth
Date,right?

Thus, make a new query, bring the relevant table. Then, drag the relevant
fields into the grid, lower part of the query designer. Then, add a
condition on the birth date, such as occurring AT or AFTER the date of
1/1/1960 ( so to get only employee born in 1960 or later).

And you will have solve that problem. Simply paste what you got in the SQL
view as answer to the question (or whatever means is required for the
homework).




Vanderghast, Access MVP
 
Back
Top