DLookup dilemma

[bufbec]

Working in Access XP. I have a table (named customers) that has
several different data type names for customers. Each customer has 6
rows of data, 1 for each data type name. Each row has the customer's
individual data and some comparison data that needs to be put into a
table on a report.

In the report, each cell in the report table has a dlookup function
that needs to pulled for the 6 rows per customer from the customers
table. I can get my first customer's first row data in the table.
However, no other row for this customer produces any output.
Furthermore, each subsequent customer only gets one row of their data
from the customers table, but it is not the same row of data for each
customer (randomly selects the row). Why is this happening? Do, I need
to

If anyone can offer up some assistance at this time, I would really
appreciate it.

Thanks

 

[Jeff Boyce]

I'm having trouble visualizing the data structure you are describing...

It sounds like you have a table that has 6 rows of data per customer, to
handle different data types. This would be a common design approach ... for
a spreadsheet!

Access is a relational database, and you get the best use of its features
and functions when you feed it well-normalized data, not 'sheet data.

By the way, you do NOT need to put data into tables in order to get it back
in reports in Access. You can use Access queries against (again)
well-normalized data to generate a wide variety of report formats.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bufbec]

I'm having trouble visualizing the data structure you are describing...

It sounds like you have a table that has 6 rows of data per customer, to
handle different data types. This would be a common design approach ... for
a spreadsheet!

Access is a relational database, and you get the best use of its features
and functions when you feed it well-normalized data, not 'sheet data.

By the way, you do NOT need to put data into tables in order to get it back
in reports in Access. You can use Access queries against (again)
well-normalized data to generate a wide variety of report formats.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

I figured it would be hard to visualize. I got the data from someone
else in my company (via Excel), this report is actually only one page
of a much larger report, that is customized of course for each
customer. Can I email you a .bmp of the table and the report I am
working on so you can see what the problem is?

 

[Jeff Boyce]

The purpose (and advantage) of these newsgroups is that everyone gets to
benefit from the discussions/solutions here.

You'd have more chances to have someone's ideas by posting a more complete
description here (besides, since my time here is volunteered, any direct
emails I get I assume are seeking paid assistance <g>!).

Here's an example of a more-normalized data structure:

tblPerson
PersonID
LastName
FirstName
MiddleName
Suffix
DateOfBirth

Note that other details about the relationship of a Person to other "facts"
get handled in other tables. Check Access HELP on the topic of
"normalization". Also, check Jeff Conrad's website:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bufbec]

The purpose (and advantage) of these newsgroups is that everyone gets to
benefit from the discussions/solutions here.

You'd have more chances to have someone's ideas by posting a more complete
description here (besides, since my time here is volunteered, any direct
emails I get I assume are seeking paid assistance <g>!).

Here's an example of a more-normalized data structure:

tblPerson
PersonID
LastName
FirstName
MiddleName
Suffix
DateOfBirth

Note that other details about the relationship of a Person to other "facts"
get handled in other tables. Check Access HELP on the topic of
"normalization". Also, check Jeff Conrad's website:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Database...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Well, I thought that might be your answer. Grasping at straws as I
cannot make the data work in its current structure with the Dlookup
function. The database is alerady normalized in that the customer
demographic info is on anlther table and the current table references
back to that. I think I just need to take the time and take this
table, expand the columns to get each customer in a single row instead
of multiple rows.

Thank you for your time.

 

[Jeff Boyce]

You mentioned that the data came from Excel. It would be quite uncommon for
that data to be well normalized.

And the solution is rarely to "add more columns" to put all the data into
one row.

I urge you to check up on normalization before you redesign your table.

Regards

Jeff Boyce
Microsoft Office/Access MVP