Think it's tough one - concatenate tables

[Ramzess II]

Hello guys!

Problem - There are two tables with such fields:

1) Schools
SchoolID; SchoolName;

2) Performances
PerfID, SchoolID; PerfName; PlayDate;

(in real life there are much more fields in each table but I skip it to make things less complicated)

These two tables has a one to many relationship, like many performances for each school.

Every year I have to print out the school table so it contains fields PerfName and PlayDate as seperate columns, like this:

SchoolID; SchoolName; PerfName; PlayDate;

By using query it seems to work fine as far as I have one performance record for each school, but since there are many different performances played in every school and it makes one to many relationship for those two tables really work, it leaves me confused on how to get such query.

Thank you guys in advance!

 

[Malcy]

Hi
I think you are on the right track with your query but I think you need to develop a report so as to make the results more usable.
I think that I would work on the basis of producing the query as you do now but in my report I would group the records firstly by school ID then performance date (ascending) and then perfomace.
If you set the property of each school id to force a new page you should get each school starting on a fresh page and for each school have the performances all listed in ascending order of date.
Give a shout if further clarification might help
Hope this helps

 

[Ramzess II]

The thing is - I don't need this data in printed version. What I need is to export this data of two joined tables to Excel, where I will work with it.

Please, can anybody help me?

 

[Malcy]

Ok then, it all lies in the query but it will not be the easiest Excel worksheet to deal with I think. I don't think you need to concatenate the tables, simply export the query. It will, by the sound of it be a long and fairly unwieldy beast but should not be that complex.
Your query fields need to be
SchoolID; SchoolName; PerfName; PlayDate; and I think I would also add PerfId as well.
When you export the query to Excel you should have something along the lines of
PerfId; SchoolId; School; Performance; Date
1; 1; Flix HS; Romeo & Juliette; 14 Aug04
2; 1; Flix HS; Hamlet; 21 Sep 04
3; 2; Hisl Primary; Sleeping Beauty; 14 Aug 04
4; 3; Fred HS; Batman; 29 Aug 04
5; 3; Fred HS; Waiting for Godot; 13 Sep 04

You should then be able to use the data sort function in Excel to break it up either by school, performance or date I would have thought - or am I misunderstanding the problem?
Does this help at all?
Good luck

 

[Ramzess II]

Well, whether I didn't fully understand your solution or it is not what I need, that is why I made this explanation material in Excel. See attached file.
Since I am not good at this I might think this must be very easy to make it work the way I need, though may be it is different. Please help if you can.

 

[Malcy]

Hi Ramzess II
I think I can now see what you want to do a bit better and also why you are getting stuck.
So far as I can see Access doesn't like the type of report you wish to create. I am not saying it can't be done, but I am not quite sure how to do so without a fair bit of work.
The inherent problem with the report you provide is that as time goes on some schools may have 10 performances (i.e. a very very wide report going over three pages) and other schools will still only have one performance. With the report I show this would be easily handled by the school with a lot of performances having a long (as opposed to wide) report.
It is easy to kick each school's listing onto a new page if this helps.
I have put together a wee database with the data off your spreadsheet. There are no data input forms which you would need but it does show the relationship between the three tables, a query linking the tables together and an Access report which provides the substance of your Excel report but in the sort of format Access prefers.
I personally am not keen to export into Excel for reporting and prefer to use Access reports. Others will feel differently.
If you think you could work with this I am happy for you to come back to me and discuss input forms.
One of the things the tables do is avoid duplicating information, so tblPerformance doesn't give you the name of the play, just its ID. If you want the ID you go to the query.
Hope this helps you move it on a bit.
Good luck

 

[Ramzess II]

Malcy, thank you for your posts... I already made a wee dB with such structure as well, so I can post it to you and you don't have to make it from zero, but it seems you already did it. Anyway, you see, the reason why I export the resulting table to excel is because I dont know how to make it look like I need in Access report.
That is why I have added a sample excel file where you can see how this result actually look in excel.

First there are 26 regions in Latvia, and in each region there is a region centre (main city). The records in the resulting report are sorted by regions,
where the first goes schools in main city, and then schools in the rest of the region. As you can see in the table, they are seperated, like there is a header representing each region's main city name and region name as well.

Another thing is that I do print this report (which actually is a list of all Latvian schools) on A3 format pages, so all information fits in them if it is designed for page landscape. Then it is text position in the cells - vertical alignment must be TOP. The row height must be 20 (in Excel), so there is enough spece to write something in when I use this list.

These are the basics.
Regarding the number of performances it is like that.

For every school there must be a full information regarding last played performance (like name, date, time, sum negotiated, sum received).
For previously played performances only thing I need as a data is the performance name (which goes as a name of the column) and summ received.

And, I don't need the history older than four seasons, so that makes the maximum of 4 performances for each school. In other words, if there are more than 4 performances played in certain school, then just the last 4 of them are displayed.

Oh and one more - the way you showed in your example makes school name as a grouping header after which each performance played in that school is displayed. - One performance in one row. I need all the data in one single row.

In fact, until now I have done this thing manually - in Excel. But since it takes so much time, I would like to improve things, so that is why I decided to use Access, and moved the school data base to mdb.

At this stage - the only way I see is to enter the data for one season (in one season there will be only one performance in a single school), at the end of the season - make a query combining the school table and played performances table, export it to excel, then clear out the data from played performances table and school table, then inport new made school table back from Excel to Access and as the new season begins - add played performances. Using this way, I will have the new columns added to the table, and before importing it back to Access, I can manually remove columns representing old performnaces - the ones I don't need anymore.

Or - is there another way of doing so without excel?