Keeping Fields Together

kbreiss

Registered User.
Local time
Today, 17:29
Joined
Oct 1, 2002
Messages
228
I am doing a Project Tracking database so users can input where they are on specific projects. I have a seperate table that contains Weekly Updates. In this table there is a Proj_ID, Date and Weekly Update fields. I have a query that links my Projects table with the Weekly Update table.

On my report I would like to group the Weekly Updates together for a specific project. One right after the other.

My problem now is that I have a seperate report for the first weekly update, a seperate report for the second weekly update, etc.

Is there anyway to avoid this and get Weekly Update 1, Weekly Update 2, right under on another all for the same project?

Hopefully this is clear enough for someone to help me.

Thanks in advance,
Kacy
________
VAPORIZER.
 
Last edited:
Sounds like you want to group on ProjectID. Check out the sorting/grouping options available in the report's design view.

--Group-By Mac
 
Let me be more specific because I can not get it to do what I want it to by grouping.

Here's my query results.....

PROJ_ID---PROJ_TITLE---UPDATE_DATE---WEEKLY UPDATE
-----1-------TEST 1---------3/31/03---------UPDATE FOR 3/31
-----2-------TEST 2---------3/31/03---------UPDATE FOR 3/31
-----1-------TEST 1---------4/5/03-----------UPDATE FOR 4/5


I would like to have just one report for example, PROJ_ID 1, that includes both of the UPDATE_DATE AND WEEKLY UPDATE on the same report for both. Right now it is making two reports for PROJ_ID 1 because there are two entries....

Any ideas???

Thanks,
Kacy
________
Class action settlement
 
Last edited:
kbreiss said:
Let me be more specific because I can not get it to do what I want it to by grouping.

You went to the Sorting/Grouping dialogue, selected PROJ_ID as the grouping field, checked that Sort Asceding was turned on (default), and that Group By was set to "Each Value" (default), and Access gave you something other than your details grouped by Project ID?

Ummm... Are you sure? If you're basing that assertion on the fact that you're still seeing more than one thing that says "Project Number: 01" or whatever on the report, that's a matter of your report design, not the grouping. You need to take those controls out of your "detail" section and put them into a "group header" section (search Access help).

I'm not trying to be snotty, it's just that this is exactly what the grouping/sorting options are for, and what you're seeking to accomplish doesn't seem terribly complex... which leaves me a bit confused...

Zip the .mdb and post it here with a small amount of sample (non-confidential) data.

--Puzzled Mac
 
I have attached a sample database....Open the Report and you will see that there is a new report being created each time for the same project, the only thing different is the Weekly Update field. I would like one report per project, but lists all of the Weekly Update fields one after the other for each week.

I appreciate your help

Thanks,
Kacy
________
Suzuki Alto History
 

Attachments

Last edited:
First Aid

Ok, there's a couple of things you need to do. This IS a sorting/grouping problem (but stemming from the query), coupled with a design problem. The basic solution to your problem is that your underlying query should only have ONE project id field, whereas you brought in one for each table. Just bring in the one from the main projects table, group on that, and put all the info that relates to the PROJECT, as opposed to the WEEKLY REPORT in the GROUP HEADER rather than the detail section of the report.

Here are some other points:

You'll make life easier for yourself if you adopt the defacto naming conventions described in numerous other posts in these forums and elsewhere. Looks like you're starting to, but it needs to be done consistently.

TABLES: Why do you have fields named BLANK in all your tables?

Table TEST_PROJECTS:

What does the field DETAIL_EXPENSE store? Do you really need a memo field for this?

If you have a whole table tracking weekly update items, what purpose does the WKLY_UPDATE field in this table serve?

Monetary info should be stored in fields of type currency.

Are ACTION's supposed to be just once for the project, or on each update? If this changes for each update, you should consider moving this field into the tblWeeklyUpdates table.

RELATIONSHIPS: Should be created on the relationships design view--that way you won't have to re-create them for every query.

REPORT: Remember to take your margins into account when designing a report--your controls are spaced out way too wide.

Check out the attached.

--Reporting Mac
 

Attachments

First of all, thanks for responding... you've been a BIG help!!
I wasn't able to convert the db that you sent. I use '97. Is there any way you would convert this to '97 so I can view it?

I'm going to attempt to answer some of your questions.... Please let me know if you have any insight on them.

TABLES: Why do you have fields named BLANK in all your tables?
--- I have several checkboxes that I use to narrow down searches. For example, say the user wants to find all the projects under manager "X" They click on the check box next to the desired manager and the checkbox updates the field "BLANK" to -1. I then use a query that searches for "BLANK" to be = -1.

What does the field DETAIL_EXPENSE store? Do you really need a memo field for this?
---you're right....I need enough space for about a paragraph or so. How big can you make a text field?

If you have a whole table tracking weekly update items, what purpose does the WKLY_UPDATE field in this table (TEST_PROJECTS) serve?
---when I started this database I used the ONLY one WKLY_UPDATE field in the TEST_PROJECTS table. So each week users just went into the form entered the date and the weekly update on this same field. So all of the weekly updates were being stored in the ONE memo field WEEKLY_UPDATE. Well, I'm worried about it getting bigger than 66,000 char. So that is why I'm working on a new table that includes only weekly updates.

Thanks again for all the help.

Kacy
________
Gpix concept
 
Last edited:
kbreiss said:
Is there any way you would convert this to '97 so I can view it?
Whoops! Sorry about that. Hopefully you can open this one...

Responding to your responses...
I then use a query that searches for "BLANK" to be = -1.
Seems like you're building a ladder to reach the top of a staircase... What does using this indirect route allow you to accomplish that you couldn't by just building your query on the actual values?
I need enough space for about a paragraph or so.
Nope, stick with a memo for that much text. I was just trying to get a clearer picture of what each field was for.
So that is why I'm working on a new table that includes only weekly updates.
This is also much better for normalization. Each table should be "about" only one thing.

--Abnormal Mac
 

Attachments

This is great! You have helped me out so much and have taught me a thing or two. Still a little confused on the " What does using this indirect route allow you to accomplish that you couldn't by just building your query on the actual values?" , but oh well. I've got what I was really looking for.

Thanks again and again and again,

Kacy
________
Body Science
 
Last edited:
Happy to help.

The "indirect route" question was simply that I don't understand why you're going through the trouble of putting extra fields in your tables to run searches (queries) to find information, when you could just base the queries on the relevant fields directly. Looking back at your earlier posts, I think you should search Access Help and the forums here on the terms "multiselect listbox".

Anyway, glad your db is working to your satisfaction.

Shout out if you hit another rough spot, and most importantly, keep reading as many of the posts in these forums as you can--you'll learn more here than in any book.

--Direct Mac
 
Last edited:
ok...one more question. On the report you have to different headers....1.) Page Header and 2.) autoProjectKey Header. How do you create the two header. All that I am seeing is the report header and the page header.

Thanks again,
Kacy
________
Lovely Wendie
 
Last edited:
Are you sure you gave the sorting/grouping options of the report design a thorough look? ;)

From Access Help:
GroupHeader, GroupFooter Properties

You can use the GroupHeader and GroupFooter properties to create a group header or a group footer for a selected field or expression in a report. You can use group headers and footers to label or summarize data in a group of records. For example, if you set the GroupHeader property to Yes for the Categories field, each group of products will begin with its category name.

Setting

The GroupHeader and GroupFooter properties use the following settings.

Setting Description
Yes Creates a group header or footer.
No (Default) Doesn't create a group header or footer.


You set these properties in the Sorting And Grouping box.

You can set these properties only in report Design view.

Note You can't set or refer to these properties directly in Visual Basic. To create a group header or footer for a field or expression in Visual Basic, use the CreateGroupLevel method.

Remarks

To set the grouping properties — GroupOn, GroupInterval, and KeepTogether — to other than their default values, you must first set the GroupHeader or GroupFooter property or both to Yes for the selected field or expression.

Always check help first and search these archives second.

--Enabling Mac
 
Last edited:

Users who are viewing this thread

Back
Top Bottom