Report layout

[reglarh]

Not sure that the title is correct but here goes......

As part of a bigger database, I have a table consisting of two main fields, a member ID and a interest code. A member will have, in practice, from 0 to 10 interest codes, although there are up to 100 interest codes in total.

I need to create an report that lists a member's interest codes across the page, not down the page. These will be in interest code sequence, but there are to be no embedded empty columns. Hence the report will be no more than 10 columns wide. After that, and the main requirement is to create an Excel spreadsheet of the information through an export function.

All without the use of VB if possible!

Any help appreciated

 

[JHB]

All without the use of VB if possible!

I don't think it is possible without use of VBA, because what you are looking for is a dynamic report!

 

[plog]

I vote possible without VBA. I need to know more about your data and your desired output.

These will be in interest code sequence, but there are to be no embedded empty columns

What does this mean exactly? Can you provide some sample data from the relevant table (include table and field names, delimit fields by commas), then based on that sample data show me what the report should look like.

 

[reglarh]

Sorry for the slow response but I have been away for a few days.

The only database I now have has personal information in it so I can't really post it, but I can describe very easily the requirement.

I have three tables:

Members (of an organisation) - 600 approx
Groups (within the organisation) 70 approx
Group membership 1000 instances

The group membership table merely holds the member number and the group number. A member can be a member of more than one group, always less than 10.

I have a report that lists each member, and in subsequent lines for each member a list of groups to which he belongs. So if he belongs to 10 groups the report will contain his name and other details on one line followed by 10 more lines.

What I want is a report that uses just one line per member, with the groups to which he belongs listed across the page in the same line. Group codes are just 6 characters long.

Is this possible?

Many thanks

 

[plog]

Post actual table and field names of all relevant fields please. Also tell me if any intuitively numeric fields (GroupNum) are not in fact, numeric.

 

[reglarh]

Table 1 name : Members

Relevant field is MemberNumber, numeric

There are another 30 fields but none of the relevant to the problem.

Table 2 name: Groups

Relevant filed is GroupCode, Text field, 6 characters long e.g. His01, Sci061 etc.

Table 3 name: GroupMembers

Relevant fields are:

GMMember Numeric
GMGroup Text, 6 characters

These fields are linked back to table1 and 2 to enforce referential integrity.

 

[plog]

FYI to everyone, this thread number is my birthdate. You're all over a month late this year, but message me for where to send gifts next year to make it up to me.

reglarh, this can be done just via queries, but you are going to need a new table called InterestCount with one numeric field called InterestNumber. In it you will have 10 records, with values from 0 to 9. Set that up.

InterestCount
InterestNumber
0
1
2
...
8
9


This table will force everyone to have 10 interests by creating a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product) with every member. This is that sql:

SELECT Members.MemberNumber, InterestCount.InterestNumber
FROM InterestCount, Members;

Call that 'sub_MemberInterests_1'. Next, create a query that gets every member's interest and assigns a rank to them so that we can order them (1-10) and then make our final query. Use this SQL:

SELECT GroupMembers.GMMember, GroupMembers.GMGroup, 1*(DCount("[GMMember]","GroupMembers","[GMMember]=" & [GMMember] & " AND [GMGroup]<'" & [GMGroup] & "'")) AS InterestNumber
FROM GroupMembers;

Name that query 'sub_MemberInterests_2'. Now, we merge the two to get the result you want. Use this SQL:

TRANSFORM Min(sub_MemberInterests_2.GMGroup) AS MinOfGMGroup
SELECT sub_MemberInterests_1.MemberNumber
FROM sub_MemberInterests_2 RIGHT JOIN sub_MemberInterests_1 ON (sub_MemberInterests_2.InterestNumber = sub_MemberInterests_1.InterestNumber) AND (sub_MemberInterests_2.GMMember = sub_MemberInterests_1.MemberNumber)
WHERE (((sub_MemberInterests_1.InterestNumber)<10))
GROUP BY sub_MemberInterests_1.MemberNumber
ORDER BY "Interest_" & [sub_MemberInterests_1].[InterestNumber]
PIVOT "Interest_" & [sub_MemberInterests_1].[InterestNumber];

That query will always have those 10 interest fields for every Member no matter how much data you have in your MemberGroups table, allowing you to build a report.

 

[reglarh]

Wow! That will take me some time to absorb and my youngest grandson arrives to spend a couple of days with us in an hour! So it may be some time before I get back to you if I have any queries.

Many thanks.

 

[reglarh]

Copied it over and worked first time!

Absolutely brilliant.

Very many thanks.

Harold Reglar

 

[reglarh]

FYI to everyone, this thread number is my birthdate. You're all over a month late this year, but message me for where to send gifts next year to make it up to me.

reglarh, this can be done just via queries, but you are going to need a new table called InterestCount with one numeric field called InterestNumber. In it you will have 10 records, with values from 0 to 9. Set that up.

InterestCount
InterestNumber
0
1
2
...
8
9


This table will force everyone to have 10 interests by creating a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product) with every member. This is that sql:

SELECT Members.MemberNumber, InterestCount.InterestNumber
FROM InterestCount, Members;

Call that 'sub_MemberInterests_1'. Next, create a query that gets every member's interest and assigns a rank to them so that we can order them (1-10) and then make our final query. Use this SQL:

SELECT GroupMembers.GMMember, GroupMembers.GMGroup, 1*(DCount("[GMMember]","GroupMembers","[GMMember]=" & [GMMember] & " AND [GMGroup]<'" & [GMGroup] & "'")) AS InterestNumber
FROM GroupMembers;

Name that query 'sub_MemberInterests_2'. Now, we merge the two to get the result you want. Use this SQL:

TRANSFORM Min(sub_MemberInterests_2.GMGroup) AS MinOfGMGroup
SELECT sub_MemberInterests_1.MemberNumber
FROM sub_MemberInterests_2 RIGHT JOIN sub_MemberInterests_1 ON (sub_MemberInterests_2.InterestNumber = sub_MemberInterests_1.InterestNumber) AND (sub_MemberInterests_2.GMMember = sub_MemberInterests_1.MemberNumber)
WHERE (((sub_MemberInterests_1.InterestNumber)<10))
GROUP BY sub_MemberInterests_1.MemberNumber
ORDER BY "Interest_" & [sub_MemberInterests_1].[InterestNumber]
PIVOT "Interest_" & [sub_MemberInterests_1].[InterestNumber];

That query will always have those 10 interest fields for every Member no matter how much data you have in your MemberGroups table, allowing you to build a report.

plog - wonder if you can throw some light on a problem. I tried to extend your work by adding two extra fields from the members table,
FullName and SurName, the idea being to include the members name. sorted by surname. I couldn't get it to work!

So I added a further query to the three you provided, linking the third to the Members table and extracting the FullName and SurName. It works but when I try to produce a report based on this, ACCESS just loops. I have to cancel ACCESS completely to get out of the loop. I then repair the database just in case, but it still fails to create the report.

Any ideas?

 

[plog]

Crosstabs are a dangerous thing, they take a lot of computational power. What's the SQL of this new query?

 

[reglarh]

I am away from the PC now using an iPad.

I created a new query using your last query and the Members table, linking on member number and extracting the member's full name and surname. The query runs, producing exactly what I want, sorted on surname.

It's when I design a report using the new query as the data source that the system hangs. It won't produce the report layout, let alone produce the report populated with data.

I then tried to modify your SQL to extract the two extra fields from the members table, but this then gave further problems, as well as not being able to sort the output of he final query into surname sequence.

A final attempt to make a table based on my new query also failed to run. I thought that having so many queries linked together might be confusing the system, and saving the output of the final query to a table would cut through the complexity.

Harold

 

[plog]

Cross tabs are a resource hogging beast. I like your idea of making a table then running the report off that. Instead of using your query (crosstab + members), make a table just out of the crosstab query. That's the real culprit in all of this.

Once you can make that a table, you should be able to link the members to that made table and get all the data you want in a query that should run quickly.

 

[reglarh]

This what I did but the table created from the crosstab query ( your final query) I expected to resemble the output of the query I.e. One row per member, but it produced a table 10 times that size!

I have been suffering from painful sciatica today and have been ordered off the PC by she who must be obeyed! I'll have a go tomorrow. If I reply to my last posting (I.e. This one) will you receive it?

If not how do I ensure you receive it?

Harold

 

[plog]

Just reply back to this whenever you can and I will be notified.

 

[reglarh]

Dear plog

I repeated my approach again this morning i.e. taking your 3rd query and linking the output to my members table, adding the two extra fields to make a fourth query. This produced the results I wanted in datasheet layout, taking about 1 minute. I then created a report based on the fourth query and by the time I finished my shower the results were on the screen!

However, I had inadvertently left the surname on the layout and there was truncation of other fields. I then went into design mode (which took about 3 minutes!) and deleted one heading item. This took about 5 minutes before the system responded. My next task, to reduce the size of other fields ran for 10 minutes before even selecting the fields I needed, the cpu usage running at about 60% during this period, all down to ACCESS. Making the changes to the size of the 10 interest fields is still running after 15 minutes. I have not yet been able to reduce their size! During this time, I get the message the 'ACCESS is not responding' although it finally does respond. This is obviously unworkable. I fear that ACCESS is having to work through the complexity of 4 sequential queries, including the crosstab query, each time I make a single layout change. At this rate, it will take about an hour to make minor changes to the layout. Yesterday, it hung continuously and I had to kill the process. After opening the database each time I did a repair just in case.

Do you have any idea what ACCESS is doing?

I will now try to revert to making my 4th query create a temporary table and report from there, but that caused ACCESS to hang yesterday!

 

[plog]

Like I said, a cross tab is a hog. When you open anything downstream of the crosstab (another query or report) it has to run it and that takes a ton of resources. You're idea of making a table out of it, setting the report to that table and then doing design work on the report is a good idea.

 

[reglarh]

When I make a table from your 3rd query I expected to get a similar layout, but instead of 624 entries I get 6240. I have now run out of ideas on how to create a table in the same form as the query output.

My problem is that when I release my system for live running it will be used by people who are not fully IT literate. Otherwise I would export the query output to an Excel spread sheet and then reimport from that, or just run an Excel report.

Unless you can come up with an idea on how I can persuade your 3rd query to create a table I am stuck!

Harold