Query to shows a comparison of two products having 3 matching fields

1 New Member

Hi friends, could you please help me, I'm trying to create a query from a table with fields Year, Location, Store, Product, AcqPrice, SalePrice.
I would like to get averages of AcqPrice and SalePrice of two selected Products (as a comparison) by Location but those products must have been sold in the same Year, Location and Store; Example:
Year Location Store Product AcqPrice SalePrice
2010 1 1 RedTshirt 20,00 28,00
2010 1 1 BlueTshirt 20,00 25,00
2010 1 2 BlueTshirt 20,00 26,00
2010 1 2 RedTshirt 20,00 26,00
2010 1 3 BlueTshirt 20,00 25,00

In this case RedTshirt and BlueTshirt where sold in Stores 1 and 2 and blue was sold in Store 3 so I like to get the average of those that have the same Year, Location and Store for both Products, showing for this example as follows:
Year Location Product AcqPrice SalePrice
2010 1 RedTshirt 20,00 27,00
2010 1 BlueTshirt 20,00 25,50

Hope you can help me....

Best Regards

Feb 23 '11 #1

Subscribe Reply

1142

code green

1,726

Recognized Expert Top Contributor

Please show the query you have so far and explain what it is doing wrong

Feb 24 '11 #2

Similar topics

4317

Query shows no data in data view

by: | last post by:

I have an ACCESS db from a DEXA machine (bone scanner). The data is in seperate tables and I want to link them so that I can query and create one table with all the data I need from the seperate...

Microsoft Access / VBA

1346

Query to Build List of Existing Fields in a Table

by: Joe User | last post by:

This may sound silly, but is it possible to build a query to generate a list of fields in a given table? TIA Joe

Microsoft Access / VBA

8132

Query for records with no matching records in another table?

by: Sven Pran | last post by:

Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...

Microsoft Access / VBA

1629

Updateable Query To List Products Ordered

by: Jack | last post by:

TblProduct ProductID ProductName Selected (Y/N) TblOrder OrderID CustomerID TblOrderDetail

Microsoft Access / VBA

1750

Want query not to require data in fields.

by: KayCee | last post by:

I am using Access 2000 I have three tables I am using to create a query that will be used for a data entry form. Table one - Client List, Table two - Services, Table three - Due Dates. I want...

Microsoft Access / VBA

3488

Add a ranking to a sorted query for comparison

by: AnndieMac | last post by:

I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been...

Microsoft Access / VBA

4875

how to make sql query for comparison condition

by: hazahafiz | last post by:

hye guest.. anybody can help me how to make sql query for comparison condition. in case,i want to generate report between range two date for example 1/2/2007 until 20/2/2007?

MySQL Database

1307

Stumped on update query between tables with no Join fields

by: KC-Mass | last post by:

I have two tables related to sales transactions and assigned areas. The tblSalesArea has SalesAreaID an autonumber StartZip, Text EndZip, Text The tblSales has many fields detailing all the...

Microsoft Access / VBA

1870

Access '07: refine SQL so query shows unique values with totals instead of all values

by: Margie | last post by:

It has been a while since my last visit, but first off, I would like to thank everyone who helped me creating my first movie database some two years ago. Although it worked pretty good, it made me...

Microsoft Access / VBA

2262

VBA to update matching fields from one table and set unmatching fields to null

by: KPR1977 | last post by:

In my Access Database in Table1, I have one record that looks something like this. (eg. "fldProd" represents field name, while "Apple" represents field value.) fldProd - Apple (text value) fldPIO -...

Microsoft Access / VBA

7037

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

6904

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

7034

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

6732

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

4768

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

2990

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

2976

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1294

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

C# / C Sharp

558

php

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP