Select a record based on the value of another record

[zach028]

I don't think this is possible, but I'll ask anyway...

I have a pre-formatted, 2-column table that looks something like this..
101
1 #
2 #
3 #
102
1 #
2 #
3 #
103
1 #
2 #
3 #
etc...

I need to summarize 1,2,3 based on the value that precedes them (i.e. add 1's for 102,105,110). Running a cross-tab is making a mess because all of the categories are in one column. Can anyone help?

 

[kathryn]

Sorry, but you really can't do much with your table designed as it is.

You need to make it a THREE column table

101 1 #
101 2 #
101 3 #
102 1 #
102 2 #
102 3 #

Do you have the ability to redesign the table? Is the table coming from somewhere else?

Kathryn

 

[zach028]

The table comes from another program as a .prn file. I just figured out that if I import it to Excel first with fixed column widths I can split the categories. This seems like too much work though as I would still have to type in 3 101's, 3 102's, etc. With the volume I'm working with, I might as well use a calculator. Any way for Access to pass that value down?

Here's an examle of what I have now..

101
1 #
2 #
3 #
102
1 #
2 #
3 #

Thanks,
Zach

 

[kathryn]

It's time to go pick up my daughter; let me think about this overnight. I will post in the AM.



Kathryn

 

[Mongooses]

I take it that 101, 1,2,3 are all in the same field (column) and that there is a number in the second field. If that is the case why not create a query that sums all the values in the second column if the first column equals 1 and then do another query for 2 and another for 3?

Just go to design view for a query add the two fields to the query, click on the sigma (the greek letter - it will say "Totals" when you place the mouse pointer over it), then for the field that has the 1,2 or 3 change the "Total" property to "Where" and in the "Criteria" property change it to ="1" or ="2" or ="3". In the field that contains the values you want to sum change the "Total" property to "Sum"
This will sum all your 1's or 2's or 3's.

Now I might have missed the mark if you have many 101's and want to sum all the 1's for 101's and all the 1's for 102's. If that is the case I will offer another suggestion for summing like that.

HTH
jc

 

[zach028]

Mongooses, I've been able to separate the '101' from the 1,2,3 so they are now in different columns.

My problem is that I need to do several summaries with multiple categories each (e.g. 101,103,107 and 102,104,105,106)

Thanks for the input,
Zach

 

[kathryn]

Zach,

Are you still working on this? I got slammed with work and haven't checked back. I don't work on Mondays, but if you need help, post. I have email notification turned on.
Kathryn

 

[dneufarth]

Zack,

Looks like the report has group headings and then detail lines for each group. A product like Monarch or possibly Data Junction allows you to take reports and create tables.

As a workaround for now, take the your Excel worksheet and make these changes (I'm assuming 101 is in column A and 1,2,3 are in column B):

select column B and insert a column (your data is now in A and C

select cell B1 and enter =A1

select cell B2 and enter =IF(AND(ISNUMBER(A2),ISBLANK(C2)),A2,IF(AND(ISBLANK(A2),ISNUMBER(C2)),B1,"&quot)

copy cell B2 and paste in B3 thru last row of data

Your data should now have the proper number from the group header in each detail row and you no longer need column A. If you want to get rid of column A, copy columns B thru whatever column you need and do a paste special (values only) in another group of cells or new worksheet.


You can now import the data or refer to it directly. A query that ignores nulls in the detail field (C1, c2, ... in the Excel worksheet) can be used to remove the header records from the table or you can sort in Excel and just manually delete appropriate rows.

Dave

 

[zach028]

Dave-
Thanks for the formula. I had to adjust a bit since there are labels in all the fields in column B, but it is working well now like this:

=IF(AND(ISNUMBER(A2)),A2,IF(AND(ISBLANK(A2),ISTEXT(C2)),B1,"&quot)

That should take care of all of my problems. Thanks again to all who helped.

Zach