Need all fields from both tables

[Bonnie A]

Hi everyone! Using A02 on XP. I need a table with about 300 fields and of
course, I hit the brick wall way before then. I created 2 tables and both
have a unique field [PolNum] that is indexed with no duplicates allowed. The
tables consist of a policy number and the 50 zillion funds they could use. I
fit them all into 2 tables and have [PolNum] in both. What is the best way
to pull 'all' of the fields from both tables into one query? I need to see a
policy number of GP07442 followed by ALL of the fields from both tables.

Any help or advice would be SO much appreciated!!!

Thanks in advance for your time.

 

[Bob Barrows [MVP]]

Hi everyone! Using A02 on XP. I need a table with about 300 fields
and of course, I hit the brick wall way before then. I created 2
tables and both have a unique field [PolNum] that is indexed with no
duplicates allowed. The tables consist of a policy number and the 50
zillion funds they could use. I fit them all into 2 tables and have
[PolNum] in both. What is the best way to pull 'all' of the fields
from both tables into one query? I need to see a policy number of
GP07442 followed by ALL of the fields from both tables.

Are you saying you have a field for each fund???

In database design, think narrow and long rather than wide and short.

You were correct that you needed two tables: you just went about it the
wrong way. If I understand you correctly, here is the correct way:

Policies - contains non-fund-related data
PolNum - PK - by definition, unique
other fields describing the policy

PolicyFunds
PolNum PK
FundName PK
other fields describing that fund

Policies will contain one record for each policy. PolicyFunds will
contain as many records for each policy as there are funds for that
policy.

If I have misunderstood your situation, let me know.

 

[Bonnie A]

Hi Bob,

Yes, I understand what you are saying. My problem is that I've been
presented with the tables already built just not linked. I'd rather present
them with a query that pulls ALL of the data from both tables rather than
spend a few hours redesigning their data. They just want a spreadsheet-type
arrangement. What type of SQL would do that?

--
Bonnie W. Anderson
Cincinnati, OH

Hi everyone! Using A02 on XP. I need a table with about 300 fields
and of course, I hit the brick wall way before then. I created 2
tables and both have a unique field [PolNum] that is indexed with no
duplicates allowed. The tables consist of a policy number and the 50
zillion funds they could use. I fit them all into 2 tables and have
[PolNum] in both. What is the best way to pull 'all' of the fields
from both tables into one query? I need to see a policy number of
GP07442 followed by ALL of the fields from both tables.

Are you saying you have a field for each fund???

In database design, think narrow and long rather than wide and short.

You were correct that you needed two tables: you just went about it the
wrong way. If I understand you correctly, here is the correct way:

Policies - contains non-fund-related data
PolNum - PK - by definition, unique
other fields describing the policy

PolicyFunds
PolNum PK
FundName PK
other fields describing that fund

Policies will contain one record for each policy. PolicyFunds will
contain as many records for each policy as there are funds for that
policy.

If I have misunderstood your situation, let me know.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

It is just not possible. A single query cannot do this.

From Access Query Specifications:
Number of fields in a recordset 255

There is no magic wand we can wave that would all of a sudden allow a
query to return more fields ...

Heck, even an Excel spreadsheet is limited to 256 columns ...

Do you need help redesigning their data? I'm sure we can help you cut
down the time it will take...

Hi Bob,

Yes, I understand what you are saying. My problem is that I've been
presented with the tables already built just not linked. I'd rather
present them with a query that pulls ALL of the data from both tables
rather than spend a few hours redesigning their data. They just want
a spreadsheet-type arrangement. What type of SQL would do that?

--
Bonnie W. Anderson
Cincinnati, OH

Hi everyone! Using A02 on XP. I need a table with about 300 fields
and of course, I hit the brick wall way before then. I created 2
tables and both have a unique field [PolNum] that is indexed with no
duplicates allowed. The tables consist of a policy number and the
50 zillion funds they could use. I fit them all into 2 tables and
have [PolNum] in both. What is the best way to pull 'all' of the
fields from both tables into one query? I need to see a policy
number of GP07442 followed by ALL of the fields from both tables.

Are you saying you have a field for each fund???

In database design, think narrow and long rather than wide and short.

You were correct that you needed two tables: you just went about it
the wrong way. If I understand you correctly, here is the correct
way:

Policies - contains non-fund-related data
PolNum - PK - by definition, unique
other fields describing the policy

PolicyFunds
PolNum PK
FundName PK
other fields describing that fund

Policies will contain one record for each policy. PolicyFunds will
contain as many records for each policy as there are funds for that
policy.

If I have misunderstood your situation, let me know.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Bonnie A]

Hi again Bob,

I took liberties with the DB (after I made a copy) and created tFundList and
tPolicyFundData and it works and they are happy.

Thank you very much for redirecting my visual. I stayed stuck on short and
wide. (I wish I could redesign some of my older work.)

Thank you for taking the time to help.

--
Bonnie W. Anderson
Cincinnati, OH

It is just not possible. A single query cannot do this.

From Access Query Specifications:
Number of fields in a recordset 255

There is no magic wand we can wave that would all of a sudden allow a
query to return more fields ...

Heck, even an Excel spreadsheet is limited to 256 columns ...

Do you need help redesigning their data? I'm sure we can help you cut
down the time it will take...

Hi Bob,

Yes, I understand what you are saying. My problem is that I've been
presented with the tables already built just not linked. I'd rather
present them with a query that pulls ALL of the data from both tables
rather than spend a few hours redesigning their data. They just want
a spreadsheet-type arrangement. What type of SQL would do that?

--
Bonnie W. Anderson
Cincinnati, OH

Bonnie A wrote:
Hi everyone! Using A02 on XP. I need a table with about 300 fields
and of course, I hit the brick wall way before then. I created 2
tables and both have a unique field [PolNum] that is indexed with no
duplicates allowed. The tables consist of a policy number and the
50 zillion funds they could use. I fit them all into 2 tables and
have [PolNum] in both. What is the best way to pull 'all' of the
fields from both tables into one query? I need to see a policy
number of GP07442 followed by ALL of the fields from both tables.

Are you saying you have a field for each fund???

In database design, think narrow and long rather than wide and short.

You were correct that you needed two tables: you just went about it
the wrong way. If I understand you correctly, here is the correct
way:

Policies - contains non-fund-related data
PolNum - PK - by definition, unique
other fields describing the policy

PolicyFunds
PolNum PK
FundName PK
other fields describing that fund

Policies will contain one record for each policy. PolicyFunds will
contain as many records for each policy as there are funds for that
policy.

If I have misunderstood your situation, let me know.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

You're welcome. At least you'll be better off going forward

Hi again Bob,

I took liberties with the DB (after I made a copy) and created
tFundList and tPolicyFundData and it works and they are happy.

Thank you very much for redirecting my visual. I stayed stuck on
short and wide. (I wish I could redesign some of my older work.)

Thank you for taking the time to help.

--
Bonnie W. Anderson
Cincinnati, OH

It is just not possible. A single query cannot do this.

From Access Query Specifications:
Number of fields in a recordset 255

There is no magic wand we can wave that would all of a sudden allow a
query to return more fields ...

Heck, even an Excel spreadsheet is limited to 256 columns ...

Do you need help redesigning their data? I'm sure we can help you cut
down the time it will take...

Hi Bob,

Yes, I understand what you are saying. My problem is that I've been
presented with the tables already built just not linked. I'd rather
present them with a query that pulls ALL of the data from both
tables rather than spend a few hours redesigning their data. They
just want a spreadsheet-type arrangement. What type of SQL would
do that?

--
Bonnie W. Anderson
Cincinnati, OH


:

Bonnie A wrote:
Hi everyone! Using A02 on XP. I need a table with about 300
fields and of course, I hit the brick wall way before then. I
created 2 tables and both have a unique field [PolNum] that is
indexed with no duplicates allowed. The tables consist of a
policy number and the 50 zillion funds they could use. I fit
them all into 2 tables and have [PolNum] in both. What is the
best way to pull 'all' of the fields from both tables into one
query? I need to see a policy number of GP07442 followed by ALL
of the fields from both tables.

Are you saying you have a field for each fund???

In database design, think narrow and long rather than wide and
short.

You were correct that you needed two tables: you just went about it
the wrong way. If I understand you correctly, here is the correct
way:

Policies - contains non-fund-related data
PolNum - PK - by definition, unique
other fields describing the policy

PolicyFunds
PolNum PK
FundName PK
other fields describing that fund

Policies will contain one record for each policy. PolicyFunds will
contain as many records for each policy as there are funds for that
policy.

If I have misunderstood your situation, let me know.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.