DLookup or similar function

  • Thread starter Thread starter RDiva
  • Start date Start date
R

RDiva

I need to return multiple values into a single field on a form based on
criteria that identifies the correct values to be returned. For example: I
have suppliers which ship to mulitple stores across multiple regions. I want
to group the total sales for each supplier by region and identify all the
store locations which they ship to in a single field. This information is
stored in a form where each store manager can then input comments about each
supplier that ships to their store. Can I group total sales by supplier and
region and identify all stores in a single field?
 
RDiva said:
I need to return multiple values into a single field on a form based on
criteria that identifies the correct values to be returned. For example: I
have suppliers which ship to mulitple stores across multiple regions. I want
to group the total sales for each supplier by region and identify all the
store locations which they ship to in a single field. This information is
stored in a form where each store manager can then input comments about each
supplier that ships to their store. Can I group total sales by supplier and
region and identify all stores in a single field?
Click to expand...

Information is stored in tables and displayed in forms - an important
distinction. It's a BAD IDEA to store multiple values in a single field
- so bad that rule number 1 of database design ("1st normal form")
exists to tell you not to do this (Google for "normalisation" and
"normal forms"). Usually, when you're tempted to do this, the thing you
should be doing is creating multiple records. Can be hard to get your
head around this initially, but it then becomes obvious (showing yet
again what an enigma the word "obvious" is!). You can do what you like
in forms - just use string operations. But if you structure your data
badly in tables you'll make life very, very hard for yourself.

Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial

Phil, London
 
Back
Top