can't get rid of duplicates

[lipin]

I have a table with carton numbers and the audited errors found in each one. Need 2 reports, 1st just showing the cartons audited. Second shows more detail with errors for each carton, well in the first report I would like just one detail line for each carton, I tried hide duplicates in the carton # property, well it hid the carton # dups, but the rest of the records still shows. Do I need to do this in the query or in the report? And How?

 

[yellow]

I would do it in the query. Do you have 2 seperate queries for each report? In the first report query you need to Group your data, this will remove the duplicates. Only put the fields in the query that you wish to output to the report.

 

[lipin]

Do I do this grouping in the Group By line of the query?

 

[yellow]

In the Totals line of the designed query set it to Group By, that is presuming you do not want to do any calculations on any of your other fields.

 

[NShafranski]

If you are using a query with at least one join you can use the "SELECT DISTINCT" in the beginning of the query to remove duplicates. This will not work on a query with one table.
If the "SELECT DISTINCT" doesn't work you can use a self join on the same table using an alias of the table:
SELECT *
FROM RatesRaw
WHERE RatesRaw.ID IN
(SELECT R2.ID FROM RatesRaw AS R2
WHERE Exists (SELECT Hotel, Rate, System, Count(ID)
FROM RatesRaw
WHERE RatesRaw.Hotel = R2.Hotel AND RatesRaw.Rate = R2.Rate AND RatesRaw.System = R2.System
GROUP BY RatesRaw.Hotel, RatesRaw.Rate, RatesRaw.System
HAVING Count(RatesRaw.ID) > 1))
AND RatesRaw.ID NOT IN
(SELECT Min(ID)
FROM RatesRaw AS R2
WHERE Exists (SELECT Hotel, Rate, System, Count(ID)
FROM RatesRaw
WHERE RatesRaw.Hotel = R2.Hotel AND RatesRaw.Rate = R2.Rate AND RatesRaw.System = R2.System
GROUP BY RatesRaw.Hotel, RatesRaw.Rate, RatesRaw.System
HAVING Count(RatesRaw.ID) > 1)
GROUP BY Hotel, Rate, System);

Just modify for your table and field names.

 

[Mkaryl]

Thanks for taking the time to respond. Actually, the problem was not in the query, but rather in the report. I created two subreports, one to be placed in the page footer and the other for the report footer. I wanted to group sums for figures by the group date (page footer) and by the month date (report footer).

For some reason, when I placed the subreport in the page footer, the last data record in the set duplicated itself on the next to the last and the last page of the report. I solved this problem by moving my subreport to the date footer and I got the results that I wanted. But I still was not able to get the subreport to show all of my monthly totals once I placed it in the report footer. I got my results when I did the report, but lost it with the placement?!?!?!?!? Anyway, I just decided to run two reports until I can figure out what to do. Thanks again!

 

[NShafranski]

The Access reporting engine is quite ignorant in that it has to take multiple passes to calculate, then sort and/or group, then display the data. This is were the problem lies and it shows that extra record. To be honest with you I don't know how you are going to work around that. I try to help as best I can. I'll keep you posted.

 

[Mkaryl]

Oh no doubt yourule with your Access wisdom. I mean for starters, you have enlightened me as to why I had the problem to begin with and that often times is an answer in and of itself. I think that until it is known how to overcome this issue, then I will just have to do a separate report for the monthly figures, which is okay, just not as efficient. Thanks!!!

 

[NShafranski]

Could you post the SQL so I could get an idea of the structure of your DB? It may be easier to create a separate table to send the data you are reporting to with an APPEND query and running the report from it. Then the report won't have mess with the joins and understand the hierarchy. Then after it is complete just have a delete query to remove the data.

 

[Mkaryl]

SELECT [QlinkSum tbl].Date, Sum([QlinkSum tbl].BoardingOn) AS SumOfBoardingOn, Sum([QlinkSum tbl].BoardingOff) AS SumOfBoardingOff
FROM [QlinkSum tbl]
GROUP BY [QlinkSum tbl].Date;



This is the SQL for the qry I created to sum a number field. The original qry consisted of two fields the listed numbers for a count of "ON" and "OFF". I did a qry with "BoardingOn:IIf ([loadtype]="on", 1,"") and BoardingOff:IIf ([loadtype]="off", 1,""); then I used these two fields to create a sum field from qry to tbl. I may have to find the other q'ries.et me know if you need some more info. thanks

 

[NShafranski]

Yes plz that would help. I'm wondering if you could do a GROUPBY of the loadtype and then add that field again and do a count instead of a sum. I'd need to have a little more info to determine. Actually I just tried it and it works for data somewhat like yours.

SELECT Bookings.Client, Count(Bookings.Client) AS CountOfClient
FROM Bookings
GROUP BY Bookings.Client;

Just add loadtype instead of client. It first will group all of the common loadtypes then count them. One query instead of 2. Let me know if this works for you.

 

[Mkaryl]

Actually when I looked at my qry I realized that I did the IIf:[loadtype]="on", [count],"") so that it would return my number count for the "ON" field. The actual SQL is as follows:

SELECT [Qlink_Main tbl].Date, IIf([loadtype]="on",[count],"") AS BoardingOn, IIf([loadtype]="off",[count],"") AS BoardingOff
FROM [Qlink_Main tbl];


Then I did this one to sum the number by the date:

SELECT [QlinkSum tbl].[Date], Sum([QlinkSum tbl].[BoardingOn]) AS SumOfBoardingOn, Sum([QlinkSum tbl].[BoardingOff]) AS SumOfBoardingOff
FROM [QlinkSum tbl]
GROUP BY [QlinkSum tbl].[Date];


This last qry is what I uded to create my subreports for the main rpt. One was grouped for the day and the other for the month, which is the one that did not work as a subrpt in the report footer.

 

[NShafranski]

Try this:

SELECT [QlinkSum tbl].[Date], [QlinkSum tbl].[LoadType], Count[QlinkSum tbl].[LoadType]) AS CountOfLoadType
FROM [QlinkSum tbl]
GROUP BY [QlinkSum tbl].[Date], [QlinkSum tbl].[BoardingOn];

You'll only need one query instead of 2. I hope I've understood your problem. It's somewhat hard not having the DB right in front of me.

 

[Mkaryl]

I will give it a shot to see what happens. My original data, from an xls, included the total # of passengers who boarded or disembarked from a particular route as a number count. I first needed to have the numbers separate by ON and OFF; then I needed to have a sum of all passengers ON and OFF by the week and the month. Is this what your SQL is driving at as a result?

 

[NShafranski]

Oops I meant to include LoadType in the GROUPBY

SELECT [QlinkSum tbl].[Date], [QlinkSum tbl].[LoadType], Count[QlinkSum tbl].[LoadType]) AS CountOfLoadType
FROM [QlinkSum tbl]
GROUP BY [QlinkSum tbl].[Date], [QlinkSum tbl].[LoadType];

This query will first group all records by date then by the loadtype (either ON or OFF). Then count them. [CountOfLoadType] would then result in the number of passengers who are either ON or OFF.

ex:
[Date]; [LoadType]; [CountOfLoadType]
Oct9; ON ; 20
Oct9; OFF; 18
Oct10; ON ; 10
Oct10; OFF; 15

 

[Mkaryl]

Now that really makes sense. I could have simplified the entire process of summing and grouping with that SQL. Cool running. I will plug it in tomorrow and let you know how she flew. Thanks.

 

[NShafranski]

How do you pull this report? Are you using the calendar control? I could give you some code where you can pull any report, choose you interval(daily, weekly,monthly, quarterly, yearly). You just have to add in the WHERE clause of any query the "BETWEEN StartDt() And EndDt()".

 

[Mkaryl]

No, I am not using a calendar control. Actually, the original data was in a xls spreadsheet (which when printed made a wall mural of info), anyway I just converted to Access and organized the data so that the grouping of it was more orderly and organized. At some point, this data will be used to create a qry to determine ridership mileage and cost mileage by the week and by the month, which can actually be done from where I am at now with the data.


I think that your idea may actually be of assistance to me b/c it may actually resolve the issue of simplifying the data tbls for use.

 

[NShafranski]

How is the data inputted? Into Excel? There is no forms in the DB? So you import the data into Access from Excel.

 

[Mkaryl]

Exactly. I took this DB info from an xls. I created my tbls, qyrs and rpts from the xls. I did not use a form(to-date), but this may change as I receive additional information for the DB (which will also be an xls). Basically, I inherited a mess in the xls to begin with and I wanted to put it into Access for two reasons: 1) to make a more orderly report and 2) to begin assimilating the data into a format that would allow for future input and calculations based upon grouped (weekly/monthly/annually) ridership numbers.