Query returns more multiple entries - Records can't be identified

S

SAm

I posted yesterday, and i appologize that i will post something again today
on a similar note yet rephrazed entirely (btw, wasn't answered yesterday). i
think this will help others as well.

i have this situation more then once. my user wants to add a comment or some
other calculation from a table that doesn't have a transactionID. the reason
being that the records are being added based on calculated records.

in my example, I have daily transactions, so i have table that has daily
records. then i use a query to sum it by month. then my user adds a comment
for the month. then i want to run a query that will add the comments from the
table to the monthly records in a query. the problem is that sometimes it
works and sometimes it doesn't work. i don't have a handle on it. if i select
one month and add the comment it will usually work. when i add it to a range
of months, it will duplicate the comments. and sometimes it will not relate
them at all and i will get the entries multiplied. what is going on??? any
guidence???

please help me out.

thanks,

sam
 
J

John Vinson

I posted yesterday, and i appologize that i will post something again today
on a similar note yet rephrazed entirely (btw, wasn't answered yesterday). i
think this will help others as well.

i have this situation more then once. my user wants to add a comment or some
other calculation from a table that doesn't have a transactionID. the reason
being that the records are being added based on calculated records.

in my example, I have daily transactions, so i have table that has daily
records. then i use a query to sum it by month. then my user adds a comment
for the month. then i want to run a query that will add the comments from the
table to the monthly records in a query. the problem is that sometimes it
works and sometimes it doesn't work. i don't have a handle on it. if i select
one month and add the comment it will usually work. when i add it to a range
of months, it will duplicate the comments. and sometimes it will not relate
them at all and i will get the entries multiplied. what is going on??? any
guidence???

please help me out.

thanks,

sam
Click to expand...

I think you may be misunderstaning how queries and tables work.

A Query doesn't have any independent existance. The fields in a Query
are either values found in a Table, or are calculated (non-editable)
fields; these might be calculated from an expression on fields in a
single record, or in a Totals query might be the sum of fields in many
records, or the field might be an expression which doesn't use any
table fields at all.

In any case, a Comment field must be stored in A SINGLE FIELD in A
SINGLE RECORD of your table.

You cannot put a Comment field into a Totals query summing all the
records for a month, and have that comment apply just to the sum; it
either applies to a single record in the Table, or to multiple records
in the Table.

You may need an additional comment table; just how you would relate it
to your current table, I don't know!

John W. Vinson[MVP]
 
S

SAm

thanks for answering.

this how how i have it:

the user runs the query on a form, then there is a subform that stores the
comment with some fields of the query (employeeID, and Date) in CommentsTbl.
i then have a query that runs again (its actually on a report) that the user
sees all months with all the comments. the problem is, that it duplicates
based on how many times the query finds the comment for employeeID.

for example, lets say that EmployeeID 123, has a comment for the month of
jan and for feb, when the query runs it will duplicate twice for employeeID
123. lets say EmployeeID 234 has 3 comments on for jan, mar, apr then the
query will duplicate 3 times.

i also have an entire different scenerio than what the one above. i have a
query that has additional fields that need to be added to each record on the
query. for example one query has EmployeeID, Date(day), (sum for month)
HoursWorked, then i need to add additional information for each EmployeeID,
lets say rate, or simiar things. the table that stores rate will have
employeeID and date, but the date is for a month. the relationship is
EmployeeID. i find that sometimes my query will add the columns to the record
and sometimes it will duplicate itself.

basically, in both scenerios i have seperate table and use multiple queries
to process and information. then i create a query that will relate the table
to the final query. i am running these queries off large databases. they can
take some time to process. it seems to be sometimes like maze. therefore i
tried describing my issue as a general issue.

i would really appreciate further guidance,

thanks,

sam
 
Top