ohh, that’s different. I would approach that by doing a count of records. You can count the adj records, and you can count the not adj records. (this would be 2 subqueries). Then you can join them (outer join showing all of them with adj records) and the ones that come back null on the not adj records would be the ones that you are looking for.
If you want to just add that to the one that you have, try the mini-sub method that I linked to previosly. You should be able to get a count of the non-adj records and return them on your top level. Anything with a 0 is adj-only.
I have created a separate query to understand this and I think I do.
I have a subquery that finds parts that have tranType = ADJ* and counts them
I have a subquery that finds parts that do not have the ADJ* and counts them.
I have a main query that joins the 2 subqueries and shows thepartnum and the 2 counts side by side.
The rows that have a null in the count for no ADJ are the rows that are ADJ only.
How do I use this to return only the part numbers with a null in the No ADJ column?
on your top level, in the tab on the bottom for subquery criteria, you can filter buy the value. I can’t remember if you are looking for nulls or empty string, so try both.
Edit, you can also check out an except query. This looks for when something exists in one, but not the other. They aren’t used that often, but they do what you want. Check out the help files.
I tried doing a filter null constant and a filter Empty string constant.
The null returned 0 rows
The Empty string wouldn’t run. It gave: Criteria value is not defined for ‘secondTier’ table, in criteria row #1. Select filter value from the dropdown and click on links to specify filter conditions.
I have the Top Level called MainQuery
I have innersubquery called secondTier the links the next 2 queries together and gives the null fields I need.
I have innersubquery called PartsWithADJ that filters like it sounds.
I have innersubquery called PartsWithOutADJ that filters like it sounds.
Ok. So technically, you can do the levels like you have, but you really can get rid of that top level you have and make the next level down the top level.
If you use the tab that says “subquery criteria” it will filter the results of that level, so you don’t need the extra level.
That being said, as far as I can tell, what you have there should work. I would have to mess with it more to see what else is going on.
A possible problem might be the filtering on an internet (or decimal) for an empty string. Since it’s not a string. Try filtering on the part number filed instead of the counted field.
I built it separately and tested, thought it was fine. Randomly checked and found a record it let through that had a non-ADJ in it.
I got tired of guessing so I wrote a small Java program that takes a csv file of the raw data and spits out a csv file that returns only the partnums that contain ONLY ADJ trantypes, finds the highest tranNum and returns only the highest tranNum record for each PartNum.
It returned 5094 records.
I ran my query, it returned 7478 records.
I exported both to Excel and ran the conditional format highlight cells that contain same value on both partnum rows to find the differences.
When I did a pretty good spot check, all the records it didn’t highlight had some non ADJ records in them. The highlighted records were all good.
If I post my baq on here, think you can point me in the right direction for weeding it out correctly?
If you don’t have the time, that is fine. Anything you can do would be appreciated.
If they want to check what it looked like on a particular date, picking that date in the query tells it to ignore any records after that date. That way the calculations are based as of that day.
ok, so even with that date, your total on hand is only going to be the current on hand at the current cost. So it’s not a running on hand. A stock level at a certain date is a pretty tough one to calculate. And the costs are going to be the current costs. Again, a cost at a certain date is pretty tough to calculate. I just want you to be aware of that. So no matter what you do with that date parameter, the only thing it’s going to affect correctly, is the dates of the part tran table.
I’m still working on it, I will have another update shortly. While I’m doing that, can you re-explain the requirements of BAQ? Preferably something mocked up in Excel that shows what is required? I’m not 100% sure what you are after.
