If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
erick-flores wrote:
Well I guess I am going to need some coding for my report. What I am trying to do is a replica of a FoxPro application. This application produces the same report that I am trying to create. This is what the groups headers looks like: ^ Page Header Tag Num Business Team ^ Group Header 1: cmp_id=================================== Description ^ Group Header 2: stg_id==================================== Description Components Material Part# ^ Groups Header 3: cyl_id=================================== Under this group are all the different components that the cylinders can have. They are all one on top of another, like you can see one line of data, but if you start moving the texbox you can see that there are 5 texbox one of top of another. So I guess that there is some code behind this group so I will print only if there is data behind a particular component ^ Group Header 4: bom.type1 + STR(bom.id1) Same thing for these group, but this group will show the components under the cylinder components. ^ Group Header 5: bom.type2 + STR(bom.id2) Here are the last components under the components from the cylinder components ^ Detail ^ Group Footer 5: bom.type2 + STR (bom.id2) ^ Group Footer 4: bom.type1 + STR (bom.id1) ^ Group Footer 3: cyl_id ^ Group Footer 2: stg_id ^ Group Footer 1: cmp_id ^ Pafe Footer I was trying to imitate this report in Access. But now that I know Access dont more than 10 groups, I guess I need to put some code behind my Groups (4,5 and Detail). Also if you notice all the arrows for the groups are pointing up. In Access is the other way, the arrows for the groups point down. Do you have any ideas of how to create some code for my groups in Access? So I can see my report the same as I see it in FoxPro? or perhaps another complete different way, that will produce the same/similar results? It's starting to sound like you data is not normalized. There should be a single field in the record source table/query that contains the component type (e.g. Valve, Gaskets or Piston) and another field for subtype (e.g. Gasket, Rod, Nuts, etc). Maybe you even have a subsubtype for rings? Anyway, these 3 or 4 fields are what should be specified in Sorting and grouping. This would only require 3 or 4 groups, not more than 10. If you have the valves in one set of fields, gaskets in another set of field and Pistons in yet a third set of fields, you have a mess that is better dealt with in a spreadsheet where you can move things around manually till it looks the way you want. Anyway, assuming I'm wrong about how your data tables are designed. You can make a single section display in a different way depending on the type of the group by using code in the Format event procedure to make some controls visible and otheres invisible. It's pretty straightforward if a little tedious. -- Marsh MVP [MS Access] |
#22
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
It's starting to sound like you data is not normalized.
There should be a single field in the record source table/query that contains the component type (e.g. Valve, Gaskets or Piston) and another field for subtype (e.g. Gasket, Rod, Nuts, etc). Maybe you even have a subsubtype for rings? Anyway, these 3 or 4 fields are what should be specified in Sorting and grouping. This would only require 3 or 4 groups, not more than 10. Hello You are complete right. That is the way I have my data. Ok, so I need to add a single field to all of my table (after Cylinders), specifying the type, right? So I will add a field for my tables: Type1(component-type) Type2(subtype) Detail (subsubtype) Comp_valve_asb Comp_valve_parts Packing_case_asb Cups Packing_case_parts packing_set_det Gaskets Piston_rod_asb Compressor_rods Nuts Pistons Piston_rings Rider Rings unloaders Is that what I need to do? add a field that says type1 under my Comp_valve_asb, type2 under Comp_valve_parts and then the detail group will be left for the subsubtypes. Now, once I add those fields to my tables (if thats the right way to do it) how would I use this types in my reports to group by type? How do I create the query for my report? and what about some code (if needed) behind the type's groups? Again, thank you |
#23
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
What would be the expression for my Group Headers? because I add a
field for each table with the default value = "comp_valve_asb" and did the same for the other tables. But now in the report how do I match all the types from my table to a single group call type1. And also I need the ID in my groups headers. I am just complete lost in here. I understand what you told me, I can see the big pictures grouping by type1 and type2. But I dont know how to make it work. Can you please guide me here through every step, as far as, what new fields to add? where to add them? do I need to create new tables/queries? do I need to add new relationships to my tables? what would be the expression for the Groups inside my report (for the type1 and type2)? |
#24
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
erick-flores wrote:
What would be the expression for my Group Headers? because I add a field for each table with the default value = "comp_valve_asb" and did the same for the other tables. But now in the report how do I match all the types from my table to a single group call type1. And also I need the ID in my groups headers. I am just complete lost in here. I understand what you told me, I can see the big pictures grouping by type1 and type2. But I dont know how to make it work. Can you please guide me here through every step, as far as, what new fields to add? where to add them? do I need to create new tables/queries? do I need to add new relationships to my tables? what would be the expression for the Groups inside my report (for the type1 and type2)? These newsgroups do not lend themselves to tutorials or full blown designs. Specific questions about a detailed problem are the norm, but questions about how to learn about a general topic are also appropriate. In your situation, you could search the internet on Database Normalization, but you would get thousands of hits. While that may be needed eventually, you can start with this article http://support.microsoft.com/kb/283878/en-us and search for specific aspects that you want to explorefurther. Along those lines, I sometimes summarize most of that with a vague table design guideline: When editing a value (e.g. customer name) in your database, you should only have to edit one field in one record in one table. Also, when adding new data (e.g. a new kind of part), you should only have to add one record to one table (you should not have to add another field to a table's design). I don't think I understand your data well enough to design your tables. They may be vaguely like: Products table: ProductKey AutoNumber (Primary Key) Description Text Parts table: PartKey AutoNumber (Primary Key) Description Text SpecCode Text . . . ProdParts table: ProdPartKey AutoNumber (Primary Key) ProductKey Long (Foreign Key) PartKey Long (Foreign Key) SubPartOf Long (Foreign Key) to parent part in same table With the tables properly designed, the next step is to create a query to use as the report's record source. The query would join the tables appropriately so the report gets its data in a nice organized manner. How that's done depends on how many levels of sub(sub...)parts you have. For a small number of levels you can just use outer joins to multiple instances (one join for each level) of the ProdParts table. If you might have an arbitrary number of levels, then you have a BOM (bill of materials) problem that requires a more sophisticated approach (that will require a fair amount of research on your part). Specific questions about normalization and table design should be posted to the tables design newsgroup. -- Marsh MVP [MS Access] |
#25
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
I do understand what normalization is. As far as I know my tables are
well design. There are no repeated fields in any of my tables. Except for those like "description" but in this case is a description for each table. On the other hand, I guess I will need to do some more research of how to create those groups. Thanks for your time |
#26
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
erick-flores wrote:
I do understand what normalization is. As far as I know my tables are well design. There are no repeated fields in any of my tables. Except for those like "description" but in this case is a description for each table. On the other hand, I guess I will need to do some more research of how to create those groups. You never have described your tables, but I have the impression that you have one table with different fields for Compressor, Stages and Cylinders. These would be repeated fields as they are all "parts" of one kind or another. Remember my vague guideline - how many "things" would have to be changed if you added a high volume, low pressure compressor that used an impeller? It certainly should not require you to change the design of any table. The first exercise in table design is to determine the independent "entities". Each entity is then represented by a table and its fields are the "attributes" of the entity. In my view Cylinders are not an entity nor are they independent of Compressor and Stages. Therefore, you need to define an entity such as "parts" to hold that information along with any other "parts" such as rings, etc. Sometimes this is neither easy nor intuitive, but it is necessary to avoid all kinds of problems in every other aspect of your database. OTOH, maybe that's what you aready have and it's the report's record source query that's causing all the reporting problems. -- Marsh MVP [MS Access] |
#27
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
You never have described your tables, but I have the
impression that you have one table with different fields for Compressor, Stages and Cylinders. These would be repeated fields as they are all "parts" of one kind or another. Remember my vague guideline - how many "things" would have to be changed if you added a high volume, low pressure compressor that used an impeller? It certainly should not require you to change the design of any table. My tables are design as follow: locations: Table Field Name Data Type loc_id(pk) AutoNumber description Text loc_info Memo compressors: Table cmp_id(pk) AutoNumber loc_fk Number (relationship one-to-many) tag_num Text Notes Memo stages: Table stg_id AutoNumber cmp_fk Number description text Notes Memo cylinders: Table cyl_id AutoNumber stg_fk Number serial_num Text description Text Notes Memo comp_valve_asb: Table cva_id AutoNumber cyl_fk Number description Text Notes Memo comp_valve_parts: Table cvp_id AutoNumber cva_fk Number description Text gaskets: Table gsk_id AutoNumber cyl_fk Number description Text Notes Memo piston_rod_asb: Table pra_id AutoNumber cyl_fk Number description text pistons: Table pst_id AutoNumber pra_fk Number description Text Notes Memo rider_rings: Table prr_id AutoNumber pst_fk Number description Text Notes Memo piston_rings: Table ppr_id AutoNumber pst_fk Number description Text Note: All the "_id" are the Primary Key for each table, and the "_fk" are the Foreign Key for each table. All the relationships are one-to-many. I have 18 tables in total, here are only 11 tables, but they show all the possibilities that could happend. OTOH, maybe that's what you aready have and it's the report's record source query that's causing all the reporting problems. I do think that my problem is in the report;s record source query. |
#28
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
By the way, if you know a good (really good) book that explain technical stuff about MS Access (like doing this kind of groups in reports, and some others technical/difficult/hard tasks) can you please let me know which ones are out there? |
#29
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
erick-flores wrote:
By the way, if you know a good (really good) book that explain technical stuff about MS Access (like doing this kind of groups in reports, and some others technical/difficult/hard tasks) can you please let me know which ones are out there? The reference that I rely on is The Access xx Developers Handbook (where xx is the version of Access) by Getz, Litwin, ... from SYBEX. -- Marsh MVP [MS Access] |
#30
|
|||
|
|||
How can I make a group not to show/display in a report if no data is under that group?
erick-flores wrote:
You never have described your tables, but I have the impression that you have one table with different fields for Compressor, Stages and Cylinders. These would be repeated fields as they are all "parts" of one kind or another. Remember my vague guideline - how many "things" would have to be changed if you added a high volume, low pressure compressor that used an impeller? It certainly should not require you to change the design of any table. My tables are design as follow: locations: Table Field Name Data Type loc_id(pk) AutoNumber description Text loc_info Memo compressors: Table cmp_id(pk) AutoNumber loc_fk Number (relationship one-to-many) tag_num Text Notes Memo stages: Table stg_id AutoNumber cmp_fk Number description text Notes Memo cylinders: Table cyl_id AutoNumber stg_fk Number serial_num Text description Text Notes Memo comp_valve_asb: Table cva_id AutoNumber cyl_fk Number description Text Notes Memo comp_valve_parts: Table cvp_id AutoNumber cva_fk Number description Text gaskets: Table gsk_id AutoNumber cyl_fk Number description Text Notes Memo piston_rod_asb: Table pra_id AutoNumber cyl_fk Number description text pistons: Table pst_id AutoNumber pra_fk Number description Text Notes Memo rider_rings: Table prr_id AutoNumber pst_fk Number description Text Notes Memo piston_rings: Table ppr_id AutoNumber pst_fk Number description Text Note: All the "_id" are the Primary Key for each table, and the "_fk" are the Foreign Key for each table. All the relationships are one-to-many. I have 18 tables in total, here are only 11 tables, but they show all the possibilities that could happend. OTOH, maybe that's what you aready have and it's the report's record source query that's causing all the reporting problems. I do think that my problem is in the report;s record source query. While it might be possible to normalize those tables on the fly by using a UNION query, you would be far better off changing it to something more like my earlier general idea. Splitting all that info into separate tables sure looks like the culprit to me. Using the table name to identify the component is the troublemaker here. Instead you should have one table with a field that identifies the component. Since your tables are so similar, it would be easy to do by just leaving the Description, Notes and/or SerialNum fields Null for the components that don't use the field. You may want to get a second opinion from someone in the Table Design newsgroup. In my simple example tables, please take note of the "junction" table that contains the foreign keys to connect the product to its components and another foreign key to the component's parent component in the same table. This structure may not be intuitive to you and you will have to do some homework to understand how to deal with the hierarchical nature of your data, but it does allow you to construct the query needed to produce your report. Without this, I don't see how you can organize your data to get the report you want. Don't get me wrong here, the problem you are working on is not a simple problem. It requires a sophisticated table structure, at least one complex query before you can use all that to create a fairly straightforward report. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|