Query Duplication

[dancoe2004]

I have a query which is designed to combine two tables, volumes and prices. Each record should then show the price and volume at an airport per month. An airport can have multiple records per month if there are either multiple airlines operating to the airport or multiple suppliers offering a price.

The issue is that where an airport has multiple airlines for a month this creates duplicates. If there are 2 airlines then each record is repeated twice giving 4 records, if there are 5 airlines then there are 25 records. The same thing doesn't happen where there are multiple suppliers however. I'm stumped.



I do have a very limited understanding of SQL but this query was put together using the design view. The SQL is below, any help would be gratefully received.


SELECT [Actual Volumes].Country, [Actual Volumes].Airport, [Actual Volumes].Airline, Prices.[Supplier code], Prices.[%], [Actual Volumes].Date, [Actual Volumes].[Fuel Uplift USG], [Actual Volumes].[Sector Count], [Fuel Uplift USG]*[%]/100 AS [Supplier Volume], [Sector Count]*[%]/100 AS [Supplier Sectors]
FROM [Actual Volumes] INNER JOIN Prices ON ([Actual Volumes].Date = Prices.Date) AND ([Actual Volumes].Airport = Prices.Airport);

[ranman256]

it depends on your data layout.
you can query setting the property UNIQUE VALUES = true
to remove the dupes.
then count using THAT query so you dont involve the duplicates.

[ssanfu]

You have spaces in your object names - should never use spaces.

"Date" is a reserved word - should never use reserved words as object names.

"%" is an extremely bad object name.

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.