Shift data

[ironwood9 via AccessMonster.com]

I have two columns: DivisionID and RegionID - there are sometimes more than
one Region per Division, so there would be for example three records for one
division if there were three regions in one division. I want to still have 2
columns, but a only one record for each division.

So here's the current layout:

DivisionID......RegionID
....343..............17
....343..............21
....545..............37
....343..............13
....343..............11

After I run my query/procedure, it would look like:

DivisionID......RegionID
....343..............17,21,13,11
....545..............37

 

[Jeff Boyce]

Doing it this way would violate one of the basic design principles for good
database design, i.e., "one fact, one field".

If, in fact, you have a one-to-many relationship (and you do, based on your
description), then you'll need to use a relational data structure if you
want to get good use of Access' relationally-oriented features/functions.

Or would a simple Word table work better? Tell us more about what/why, not
how, if you want more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ironwood9 via AccessMonster.com]

I understand that - this is actually for a report in Excel ( I realize this
is an Access forum ), where one of my worksheets is a State Regulatory form,
and that is how they want the data to appear. But thanks for your input !

Doing it this way would violate one of the basic design principles for good
database design, i.e., "one fact, one field".

If, in fact, you have a one-to-many relationship (and you do, based on your
description), then you'll need to use a relational data structure if you
want to get good use of Access' relationally-oriented features/functions.

Or would a simple Word table work better? Tell us more about what/why, not
how, if you want more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two columns: DivisionID and RegionID - there are sometimes more than
one Region per Division, so there would be for example three records for

[quoted text clipped - 17 lines]

...343..............17,21,13,11
...545..............37