Using a query to merge data from two tables in a specific layout

[Philip]

Hi all, this is my first post so I hope I am observing all protocols.

I need to extract data from two tables and present the data in a specific format. The first table, named VSS_Initial_Extract, has personal student data and the programmes they registered for per year. There are 4089 records in this table with 45 fields each. The other table carries the grades for each module the students registered for. There are 31783 records in this table, named VSS_StudyRecord. There are 9 fields in this table. The simple query I run generates 31783 records, with 52 fields, but I need 4089 records, only with more fields so each module's details are recorded next to each student in the year they registered for them (each student registered for a different number of modules in different years). We are transferring the data from one database to another, so there is no worry about the grades changing. The old system will become redundant once the details are transferred. Here is a sample of what I have and then what I need (this is for only one student, but I need to do this for all students. I've also heavily edited so as to give an example):

What I get:

StudentNumber	VSSCourseCode	LevelCode	VSSModuleCode	Grade	Method of Delivery	Presentation Mode	Symbol
22009	CHR	3	30	74	1	1	B
22009	CHR	3	29	78	1	1	A
22009	LEA	3	1	84	1	1	A
22009	BIB	3	10	80	1	1	A
22009	CHR	3	15	76	1	1	A
22009	CHR	2	2	74	1	1	B
22009	CHR	3	23	80	1	1	A
22009	BIB	3	11	81	1	1	A
22009	BIB	3	12	74	1	1	B
22009	BIB	3	8	73	1	1	B
22009	CHR	3	19		1	1	AU
22009	CHR	3	16	82	1	1	A
22009	BIB	3	4	78	1	1	A
22009	BIB	3	5	84	1	1	A

What I need:

StudentNumber

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

VSSCourseCode

LevelCode

VSSModuleCode

Grade

Method of Delivery

Presentation Mode

Symbol

22009

CHR

3

30

74

1

1

B

CHR

3

29

78

1

1

A

LEA

3

1

84

1

1

A

BIB

3

10

80

1

1

A

CHR

3

15

76

1

1

A

CHR

2

2

74

1

1

B

CHR

3

23

80

1

1

A

Where do I start? I'm not great with SQL and tend to use the builder in Access, but if I need to get technical, I can for this one Query.

Cheers


Philip

[ranman256]

a report can do this,
set the report to 3 columns (down then right)

tho this is a very awkward way to do it ,(and is not very readable), when a report can print vertically with zero effort.

[Philip]

Hi Ranman256

You'll need to give me more guidance on Reports as I've never worked with those. I've tried the Report Wizard and it said I have chosen too many fields. Also, can reports export into Excel/CSV as I need the data in that format in order to be imported into the new database. Remember, this query will have at least 160 fields when it is working properly.

Kind regards
Philip