Normalized myself into trouble (Access XP Sp3, Windows XP)

Topic

New Reply

Hello,

What I’d like to do is have a flat, de-normalized file of, say, MainTopic and Reviewer1, Reviewer2 … Reviewer(N), when the data exists in two tables — the Main Topic table that is unique, and the Reviewer table that are the “many” to that.

On the SQL Server, I usually gerry-rig it by calling a min(Reviewer), and then call out for a sub-select for the max(Reviewer) etc., w/ comparisons in a where clause to not pick it if the two Reviewers are the same, or outer-join the Reviewer table several times over, and, well, that isn’t ideal.

I’ve tried crosstabs, but .. the Reviewers are many — anyway, I got myself in a pickle. I can use code or a nested report to get myself out of trouble, but … does anyone out there know of a brilliant way to “pretend” de-normalize my two tables?

thx
Pat

Reply | Quote

Replies

Not sure what you mean by “pretend” denormalize. You can download a free sample database with code to normalize/denormalize from Roger’s Access Library.

Reply | Quote

Thank you, I downloaded Roger’s Access Library and, the code will be perfect. Looks like I’ll have to go the code route! I’ll have to wait ’til this evening to send him a $1 via Amazon, though, so I don’t feel quite right, yet .
thx
Pat

Reply | Quote

Hi Patricia

Can you just just call a view

SELECT MainTopic.*, Reviewer.*
FROM MainTopic, Reviewer;

That will provide a completely denormalised view

Reply | Quote

Well, it doesn’t want to do exactly what I wanted, but thanks, Jezza!
I need “MainTopic – Reviewer1 – Reviewer2 – Reviewer3 ..” etc — kind of a flat pan across, just like those wide tables that are the horror of most good database people, that’s the type of layout I need now. It sounds like code is the best way to go,
thx
Pat

Reply | Quote

No probs Pat

It was a shot in the dark

Reply | Quote

But I do what to know what “QuidQuid latine dictum sit, etc etc means.” (Whoever speaks latin, will be seen as “something else?” )
thx
Pat

Reply | Quote

Click on my Jezza Link to the <—– left, and you will understand all is explained

Reply | Quote

Have a look at the thread beginning with post 514,047

It talks about using a concatentation function to bring together values from different records.

I think you could use it to concatenate the reviewers for each topic into a single field in a query, then use that for a report.

Reply | Quote

Thanks, John. I don’t think that would really work for me, in part because I the “many” side can be an unknown, non-fixed number, and the other part is — I tried something similar at first, but I still ended up w/ redundant rows, although nicely flat . I’ve modified the one that Hans put me on to fit what I need.
thx
Pat

Reply | Quote

I believe that the concat function is designed to deal exactly with the situation where you have an unknow number of rows on the many side.

For each record in the one side, find all matching values in the many and concatenate them together.

But if you have an answer that works for you, then obviously you don’t need to use concat.

Reply | Quote