Duplicate Data in a Report = erroneous data

[DanicaDear]

I have a query HOTSTICK_ORDER_DETAILS which calculates how many hotsticks a customer must pay for. The name of the field is QTY_CHARGED. (It is calculated by knowing how many sticks they order, how many they return when the sticks expire, and how many they abuse.) My goal is to print out a sheet grouping sticks by commodity numbers that shows how many sticks we can charge to a customer due to customers not returning or abusing sticks. The problem is if a customer owes for 3 sticks which are 2 different kinds of commodity numbers, the report is trying to show he owes 3 sticks in every commodity the customer originally ordered from. To see for yourself, open the attached DB. Look in HOTSTICK_ORDER_DETAILS Query. Notice ORDER_NUM 20070001 includes
1 QTY_CHARGED for COMMODITY_DESC 3775608
1 QTY_CHARGED for COMMODITY_DESC 3775756
0 QTY_CHARGED for COMMODITY_DESC 3775822
0 QTY_CHARGED for COMMODITY_DESC 3775806.
A report built off this query, rptReplenishInventory, shows
2 entries in Commodity 3775608
2 entries in Commodity 3775756
2 entries in Commodity 3775806
2 entries in Commodity 3775822.

What I *want* it to show is 1 entry, showing the correct quantity, in 3775608 and 3775756, and no entries anywhere else.

What I have tried so far:
Went to report properties...record source....which opened the Query Builder. I clicked properties there and tried to play around with "Unique Records" and "Unique Values." However, I could not achieve desired results.

Can anyone help? Thanks in advance.

[ChipR]

It sounds like you want a filter on your report "WHERE QTY_CHARGED > 0". What's the source of the box that is displaying "2" in each entry?

[DanicaDear]

The source is the QTY_CHARGED field from the query. (It's a calculated field.) I have already set the criteria of QTY_CHARGED to >0. Thanks.

[NeoPa]

Danica,

While it is sometimes necessary to attach a database to a thread, it is not required in all cases. It makes it harder to work through a problem and it fills up the site storage with too many large files. It does your cause no favours either. Members don't want to go to that much effort over each question as it means they can get far less work done. I very rarely even download a database unless it's been requested.

This seems like a simple query question, so we need the query posted in SQL, and maybe, if it's complicated, the MetaData of the relevant tables.

As a general guide, don't post a database unless asked to by someone trying to help.

This is likely to be a JOIN problem, or maybe a GROUPing problem, but we'd need to see the query to be more precise.

[DanicaDear]

Here is what I *think* you are asking for. (If not, please help me try again.)

SELECT DISTINCT [HOTSTICK_ORDER_DETAILS Query].ORDER_NUM,
                HOTSTICK_ORDER_DETAILS.COMMODITY_DESC,
                [HOTSTICK_ORDER_DETAILS Query].QTY_CHARGED,
                [HOTSTICK_ORDER_DETAILS Query].CHARGED_OUT,
                [HOTSTICK_ORDER_DETAILS Query].QTY_ABUSED,
                [HOTSTICK_ORDER_DETAILS Query].MISSING_STICKS,
                [HOTSTICK_ORDERS Query].CUST_NUM,
                HOTSTICK_ACCOUNT.PRCN,
                HOTSTICK_ACCOUNT.RT,
                HOTSTICK_ACCOUNT.ACTIVITY,
                HOTSTICK_ACCOUNT.EWO,
                HOTSTICK_ACCOUNT.PROJECT,
                HOTSTICK_ACCOUNT.LOCATION,
                HOTSTICK_ACCOUNT.FERC,
                HOTSTICK_ACCOUNT.SUB,
                HOTSTICK_ACCOUNT.RORG,
                [HOTSTICK_CUSTOMERS Query].Whole_Name
 
FROM            ((HOTSTICK_CUSTOMERS INNER JOIN
                (([HOTSTICK_ORDERS Query] INNER JOIN
                [HOTSTICK_ORDER_DETAILS Query]
  ON            [HOTSTICK_ORDERS Query].[ORDER_NUM]=[HOTSTICK_ORDER_DETAILS Query].[ORDER_NUM]) INNER JOIN
                HOTSTICK_ORDER_DETAILS
  ON            [HOTSTICK_ORDERS Query].[ORDER_NUM]=HOTSTICK_ORDER_DETAILS.[ORDER_NUM])
  ON            HOTSTICK_CUSTOMERS.[CUST_NUM]=[HOTSTICK_ORDERS Query].[CUST_NUM]) LEFT JOIN
                HOTSTICK_ACCOUNT
  ON            HOTSTICK_CUSTOMERS.[CUST_NUM]=HOTSTICK_ACCOUNT.[CUST_NUM]) INNER JOIN
                [HOTSTICK_CUSTOMERS Query]
  ON            HOTSTICK_CUSTOMERS.CUST_NUM=[HOTSTICK_CUSTOMERS Query].CUST_NUM
 
WHERE           ((([HOTSTICK_ORDER_DETAILS Query].QTY_CHARGED)>0)
  AND           (([HOTSTICK_ORDER_DETAILS Query].CHARGED_OUT)=False));

I have never even heard the word "MetaData" before. :-)

What you say about the attachments makes sense. I thought it would speed you up but I forget you all are whizes and can understand a problem and solution without ever seeing a file. (I do not understand how you do it!!) Is it possible to remove the attachment? Thanks so much for everything.

[DanicaDear]

I believe I have solved this issue. In my Query Builder for the report, I had the query and the original table shown. It was messy and I think it was pulling data twice...once from the table and once from the query. I cleaned it up a bit and poof: it worked!

PS. Should I give myself the "Best Answer" award? LOL. (I would NEVER...too undeserving!)

Thanks everyone! I am very appreciative!

[NeoPa]

@DanicaDear
That's right on the button Danica. I've changed the layout of it so that it can be read more easily (worth noting for future reference) but this is exactly the data I was hoping to work from.

Having looked through it somewhat I can see that it is quite complex in itself. The fact that some of the input sources are also queries, so could have data coming from multiple sources themselves, just adds to the complexity. I have to say that it's lucky that you found a solution, as this would be difficult to know too much about, especially without the MetaData. To liiustrate MetaDat, and hopefully give you an idea of what I'm talking about, I'll copy in a template post I use when requesting it.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[NeoPa]

@DanicaDear
We are trying to make the Best Answer more about which answer would actually be the most help for someone scanning through the list via Google, rather than awarding special points to members.

That is why I am setting some of them myself, and other moderators have been encouraged to do the same, where a thread has none. The criteria to use is to consider which post, if alone, would be the most help to an interested searcher.

In this scenario, I almost awarded it to your post (It wouldn't be the first I'd awarded to the OP of a thread). In the end though there was too little info there for anyone to benefit, though it was very useful to let us know we needn't bust any guts to find one for you.

Keep going and I'm sure you'll get one eventually. You certainly seem to be picking lots up on your way :)