Report From Normalized Tables (Access 2000)

Topic

New Reply

I have two tables: tblRecruit, tblVisitors. tblVisitors has only two fields: RecruitID and Visitor. tblRecruit and tblVisitors are linked one-many by field RecruitID. A recruit can have no visitors up to three visitors. (At least right now they can only have three – that may change in the future.) That part is no problem. My problem is trying to create a report based on these two tables with the recruit and his visitors appearing on one line. I cannot get the visitors to appear next to the recruit on the same line. The recruit name can only appear once with his three visitors appearing off to the right on one line. The report needs to be in this format:

Recruit Name Visitor1 Name Visitor2 Name Visitor3 Name

Make sense? Any help anybody can give me with this would be greatly appreciated.

Thanks,
Jon

Reply | Quote

Replies

Take a look at the attached demo. (Thrown together quickly!)
It uses a crosstab to produce a result set in the format that you want , having first allocated a Column No. using the mod 3 division. (There may be a better way of doing that part) The report is then easy.

HTH

Reply | Quote

Steve,
That works great! I had to create another query so I could concantenate first and last names in my report but that was easy. I understand your solution except for the mod 3 part. If you have a few moments, can you explain how that works? It looks like it is creating 3 columns to put each visitor name in. Thanks for your help!
Jon

Reply | Quote

The mod operator is modular division. It gives the remainder only as the answer. From a continuous sequence of figures you would get a sequence of 0, 1, 2, 0, 1, 2 etc. as your answer. Add 1 to get 1,2,3 and you can give each visit a number 1, 2 or 3. This will fall down if your Autonumber sequence has holes in it. There are other ways of generating a sequence of row numbers in a query, but they are slooooow. See Microsoft’s Report Samples demo database for an example. (I do not have the URL to hand at present)

Reply | Quote

The link for the Sample Reports database is ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center.

Reply | Quote