Gathering data based on current line

[Siiig]

Hello Accessforms!

I've done quite a bit of report work with Crystal Reports, but need to develop a report in Access now.

I have a table of data that looks something like this...

Item | Part | Price

The table results in 3-5 lines where "Item" is identical, with different parts and their prices.



Easy enough, but there are a number of items where the "Part" also has parts.

I DO have a column that can represent WHEN the part in question is made of other items, but I sadly have no clue where to even begin in Access.

My goal would be to have the report equate to something like this.

Item1 | Part1 | ###
Item1 | Part2 | ###
Part2 | Part3 | ###
Part2 | Part4 | ###

I mentioned I've worked in Crystal Reports, and I could do this pretty effectively in there, but as I said I can't even figure to myself where to start.

Any help would be greatly appreciated.

[offie]

So your end result is a query that has the data sorted?

Add the data to a table. Make both the item and part fields primary (I'm assuming that an item will always have a part)

Make a query, add the table, double click the fields you want, in the "Sort" row select Ascending for both item and part. Make sure to place Item before Part so that Item is sorted first then part.

If that isn't what you want please explain it a little more with example tables. What is this business with part within part? How is your data set up?

Edit: Realized this is in Reports, so you want a report.

Well if you want to get it right from that table then you can use the wizard and it does it automatically, likewise if you base it off of a report.

If you want something else (format) then look up a youtube video about the basics of making a report, it's really easy

[Siiig]

Edit: Realized this is in Reports, so you want a report.

Well if you want to get it right from that table then you can use the wizard and it does it automatically, likewise if you base it off of a report.

If you want something else (format) then look up a youtube video about the basics of making a report, it's really easy

I'll definitely jump on youtube to look more up, in the mean time maybe you can give me some direction as well?

Item	Part	Cost
001	P001	1
001	P002	1
001	P007	0
002	P002	2
002	P004	1
003	P005	0
003	P006	1
003	P002	0
P005	P008	3
P005	P009	1
P007	P010	4
P007	P011	2

Here's an example with random data in it. As you can see I've listed two parts "P005" as having their own parts. So they would need to be built first. What I would like (And what I've actually managed in Crystal, it's just ungodly slow), is something like this...

Item	Part	Cost
001	P001	1
001	P002	1
001	P007	0
	P010	4
	P011	2
002	P002	2
002	P004	1
003	P005	0
003	P006	1
003	P002	0
	P008	3
	P009	1

I know the spacing is awkward, but I think it serves the concept well. The final result (preferably) will let me see every component required to make an item, and their costs.

[offie]

I'll have to think about it a little more to get exactly what you want but this is what I've got so far:

First off, your data is awful, split it into 2 tables, that that is Item - Part and another is PartA - Part B
Table 1
001 - P1
001 - p2
001 - p3

Table 2
p2 - p4
p2 - p5

So then you can join them (Part in table 1, and PartA in table 2) in a query (double click the line and make it so it includes all data from Table 1)
So you would get:

001 - p1
001 - p2 - p4
001 - p2 - p5
001 - p3

Im assuimg that the cost pf p2 will be cost of p4 +p5

Then use a union to join the costs into 1 field.
To do this I would use 2 queries
-One that has the parts that only apply to the item (in previous query specify that the 3rd field "Is Null")
-Another that only has parts that have other parts (Specify "Is Not Null", only select 1st and 3rd cloums from first query)

so you'll get
001 - p1 $
001 - p3 $
~ point of union
001 - p4 $
001 - p5 $

I know it doesn't include the name of p2 but this is all I could think of...

[Siiig]

I'll have to think about it a little more to get exactly what you want but this is what I've got so far:

First off, your data is awful, split it into 2 tables, that that is Item - Part and another is PartA - Part B
Table 1
001 - P1
001 - p2
001 - p3

Table 2
p2 - p4
p2 - p5

So then you can join them (Part in table 1, and PartA in table 2) in a query (double click the line and make it so it includes all data from Table 1)
So you would get:

001 - p1
001 - p2 - p4
001 - p2 - p5
001 - p3

Im assuimg that the cost pf p2 will be cost of p4 +p5

Then use a union to join the costs into 1 field.
To do this I would make 2 queries
-One that has the parts that only apply to the item (in previous query specify that the 3rd field "Is Null")
-Another that only has parts that have other parts (Specify "Is Not Null", only select 1st and 3rd cloums from first query)

so you'll get
001 - p1 $
001 - p3 $
~ point of union
001 - p4 $
001 - p5 $

I know it doesn't include the name of p2 but this is all I could think of...

I wish I had better control over the data! Haha.

Thank you VERY much for the suggestion! I'll give it a shot with the actual data.