Select the same record multiple times

[BryanLane]

We use cycle counting for physical inventory. Each part number is assigned a three digit cycle count code where the first digit is the number of times the part is counted each year (1-4), the second digit is the month it is counted for the first time and the last digit is the week of the month it is counted for the first time. Our numbers range from 111 to 434 (not all numbers in that range are used.)

For any weekly cycle count I don't have an issue, I select the cycles for that week and get the count sheets.

I need to create a report that shows, by week, which parts will be counted. The 1## numbers will only appear in one week of the year, the 2## numbers will appear twice, the 3## three times and the 4## four times.

I tried creating a formula that assigned a value that I grouped on:

if {INVIDX.CYCCNT} in [111.00, 211.00, 311.00, 411.00]
then "01-01 Jan Week 1"
else if {INVIDX.CYCCNT} in [112.00, 212.00, 312.00, 412.00]
then "01-02 Jan Week 2"
else if {INVIDX.CYCCNT} in [113.00, 213.00, 313.00, 413.00]
then "01-03 Jan Week 3"
[continues on to the end of the year]

This formula only returns values the first time a cycle count code matches the "in" clause. So when a code comes around again(such as 411 which gets counted in the 1st week of January, April, July and October) I want it to be listed on each of the appropriate weeks.

How do handle this so that 411 appears in all four groups as well as all the other 2##, 3## and 4## items?

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[IanWaterman]

You can create records which do not really exist.

YOu will need to build a view on your database or a crystal command that creates the dummy records using a UNION query.


Ian

 

[dgillz]

I think Ian mean you CANNOT create records that do not exist. Put in another way, crystal cannot report on non existent data.

Since the item number would logically exist in your item table only once, you will only get one record on a crystal report based on this table.

You can create a view as Ian suggested.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[IanWaterman]

Thanks DGillz. Less haste more speed.

I did mean CANNOT, Doh!

Ian

 

[Madawc]

If I've understood correctly, you're wanting to show the same record in more than one group. That's not possible in Crystal, unfortunately.

Take a look at crosstabs, which can group the same data differently from the main report. You might however have to use running totals that select using several criteria, or even subreports.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BryanLane]

So then I need a report with 48 subreports (we only count on the first four Wednesdays of each month) where I specify in each subreport which cycles we are counting that week.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[dgillz]

You may be able to create a table that you could logically link to the cycle count code that would show the item 4 times.

I would need to understand how this field is populated. I saw your example and it made no sense to me at all.



Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[IdoMillet]

See my FAQ on a related topic:
faq149-3226

You can use the same approach to solve your challenge.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

How do you know what quarter these records belong to? Is there some date field?

-LB

 

[BryanLane]

There is no way to interprete the date from the cycle code because all of the 2##, 3## and 4## series of codes will be counted multiple times.

My work around was to create a cross tab report (showing suppliers as rows and branches as columns, the data is the number of part #s that will be counted that week) that uses a selection formula to select the four cycle count codes based on the week number, so that if I define the week as 1, it says to selects 111, 211, 311 & 411.

I then added 48 detail sections (we only count during the first 4 weeks of the month) and inserted the report as a subreport into each one, defining the "week" number for each sub report as a formula called @Week that has the week number as the only entry. My selection formula references this value to select the cycles.

It was not the ideal solution, but "it works."

Thanks again for all the input. Whenever I have a few minutes I drop in here and read through the posts. The information and insight is of tremendous value.

Thanks,
Bryan

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

I didn't mean from the cycle code. I meant for the purposes of your report, how do you know which quarter the cycle codes belong to, i.e., are you working with a datefield. Your solution seems extemely complex.

-LB

 

[BryanLane]

The second and third digits tell us when to select them, but it is a little obscure.

If an item is counted once a year (1##) then we count it in the in the month of the second digit and the week of the third digit. So cycle code 141 would be counted in the fourth month, first week. All of the "once a year" items are counted during the first nine months of the year (which eases the counting burden at the holidays).

The two, three and four times a year items follow a similar but the cycle number only "reveals" the first time it is counted. So cycle code 421, is counted four times a year, starting in the second month and the first week. It then repeats in the fifth month, eighth month and eleventh month (add three to the month each time 12/4 ). A three times a year item would have four added to the month, so 311 would have it's first count in month three (12/3) and then get counted in month 7 and 11. Twice a year items would have six (12/2) added to the month, so that 234, would get counted the first time in March (fourth week) and again in month.



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein