Query producing duplicate records

[jadedinvasion]

I'm having an issue with a query I'm writting in Access.

It's producing duplicate results and the best way to explain it is I have one distinct record and the query is producing it a second time.

to start off with here is my table set up

tblCategory tblfuntn tblVolume
categoryID --------->categoryID inputdate
categoryName FunctionID------>FunctionID
FunctionName busID
deptID
Volume

 

[jadedinvasion]

gah hit the wrong button! So here's my table lay out again.

tblCategory tblfuntn tblVolume
categoryID --------->categoryID inputdate
categoryName FunctionID------>FunctionID
FunctionName busID
deptID
Volume

And here is the query I'm using:

SELECT Volume, tblVolume.DepartmentID, categoryName, BusunitID

FROM (tblCategory INNER JOIN tblFunction ON tblCategory.categoryID = tblFunction.categoryID) INNER JOIN tblVolume ON tblFunction.FunctionName = tblVolume.FunctionName

WHERE format(inputDate, 'm yyyy') ='8 2006'

ORDER BY tblVolume.DepartmentID, categoryName, BusunitID, volume

and what happens is that I get replicates of each row returned, when there is only one row in the table.

Is there something wrong with how my tables are laid out?

 

[SkipVought]

Hi,

What about Select DISTINCT...

Skip,

_[red][/red]

 

[jadedinvasion]

That's the thing, they are distinct records. If I use distinct on any one of the colums, i'll be excluding rows i need.

What I have to do is sum up the volumes for the full month of August. There are multiple entries per user, per function, per day.

Lets say I want to isolate one row that I know is unqiue. Even specifiing the full inputdate, user/function/bus/department Ids, the query will return 2 rows that are identical, although only one row exists.

Does this make any sense?

 

[Golom]

Looks like there's more than one record in tblVolume that matches FunctionID where
Format(InputDate, 'm yyyy') ='8 2006'

Just to check

Select Format(InputDate, "m yyyy") As [MonthYear]
     , FunctionID
     , Count(*) As RecordCount
From tblVolume
Where Format(InputDate, "m yyyy") = '8 2006'
Group By Format(InputDate, "m yyyy"), FunctionID
Having Count(*) > 1

 

[jadedinvasion]

Oh ya, there are many many rows that have the same function ID with the same input date.

The PK for tblVolume is comprised of inputDate, UserID, FunctionID, BusID, DepartmentID.

The reason for this is that I need to allow a user to input volumes for any function, for business line, in any department for any date.

I have a feeling how I have my tables set up is kinda making this a little wonky....

 

[lespaul]

If you will "show" us the data in your tables and expected results, it's easier to help. Additionally the Understanding SQL Joins document linked below can explain why you are getting multiple values returned when there's only one record.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

... Or possibly a lot wonky.

You need to decide which of the field values that are coming from that table you want to see; use aggregate functions to select those individual values; and then GROUP BY all the other fields.

 

[jadedinvasion]

okay so...

I'm an idiot some times. Totally shouldn't have drank so much last night

What was happening what that I was linking tblfunction with tblVolume but by the function NAME not the ID. I thought maybe I could get away with it cause it was less coding. Apparently not.

There are functions that have the same name (why? because people are too freakin picky) and the query was replicating the rows that had function names that were the same as others. Not really sure if that explains it all, but i switched the link from function name to function ID and it works just dandy.

Thanks guys for your help!