Greg
?!90 product fields?! I had no idea!
I'm afraid I still don't understand the data you are working with ... "How"
depends on "what".
Yes, product as a field, not one field per product (?you are confident that
no two products have the same name?)
The normalization (perhaps you and I are using different definitions) is
both art and science, so there's no simple way to tell you what would be
"normal" in your situation (which I still don't understand too well!).
If I had an Excel spreadsheet-like set of data, with one column per product,
and something (?!?) as rows, and something (?!?) in the cells/intersections,
I still wouldn't have quite enough.
I'm guessing you have sales figures (# or $) for salespersons for months.
And stores-with-salespersons. And the salespersons never change stores or
work at more than one (?) And ...
You could use an append query to take stuff from the raw data and put it
into a more permanent structure.
More info, please!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Greg A said:
You are telling me that the raw data I have is in a format that is
difficult
to use. I knew that - hence the reason for the initial request for help.
I have already "normalized" the data to the extent my experience allows.
- I used a make table query to convert the passthrough query into a table
that I can manipulate.
- I used a mapping table to create the sales hierarchy (salesperson, mgr,
Director, VP based on the store location)
- I have an update query that converts the date into a usable format (it
was
text - converted it to date/time)
- I used a select query to group by manager and total daily sales into
monthly sales.
If I understand your recommendation, you are suggesting I should use
additional queries to convert the 90 product fields into 2 fields (product
name, and qty sold) which would allow me to do a simple crosstab query.
Can you give me a simple example of how I might do that?
Jeff Boyce said:
Greg
If "normalization" and "relational" are not familiar terms, familiarize
yourself with them.
Create a table structure (?multiple tables) that is well-normalized and
represents the data you are working with.
Figure out how to map from the structure you have now (raw data) to the
well-normalized structure you created.
Use queries to do that.
NOW you start on creating reports and forms!
Regards
Jeff Boyce
Microsoft Office/Access MVP
I understand your point, but I am not sure how to proceed. What is your
recommendation?
:
Greg
Just because the data comes in funky doesn't mean you're limited to
preserving that funkiness.
A very common approach to using Access to manage data that may be,
shall
we
say, less than well-normalized is to accept the incoming data as
"raw",
create well-normalized table structure, and use queries to "parse" the
raw
data into a structure that lets Access use its relationally-oriented
features and functions. Otherwise, you'd be trying to use Access as
if
it
were a spreadsheet.
Consider that you won't get the best (or easiest) use of Access if you
insist on feeding it 'sheet data.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have considered it. I have not been able to make it work because
of
the
format of my table. Unfortunately, the table is pulled from an
external
data
source that I cannot change.
I have approximately 90 products in the table. Each product has
its
own
field, with the number of units sold each day populated. So the
table
lays
out ilke this:
Date prod1 prod2 prod3 prod4 prod 5 .... prod 90
9/26 0 2 10 1 0 3
9/27 4 0 2 5 2 7
I am fairly new to Access, but I have not been able to figure out
how
to
get
a clean crosstab query with the table designed this way. If you
have
suggestions to help with the crosstab query design, I am open to
going
that
route.
:
Greg
Have you considered looking into a crosstab query/crosstab report
to
display
each salesperson's monthly numbers? It would save having all those
many
subreports...
Regards
Jeff Boyce
Microsoft Office/Access MVP
I am trying to teach myself access and having trouble. I have a
report
that
is laid out like this:
Salesperson Jan Feb Mar YTD
Product 1 sales
Product 1 objective
Product 1 % to objective
Product 2..
Product 3..
Etc.
Jan data is in the main report. I have a sub-report for each of
the
other
months.
All of the salespeople roll to a single manager. In the report
footer,
I
built an expression for each field:
Name: Febproduct1sales
Control: sum([product1sales])
I used the same expression in each of the sub-reports and changed
the
"visible" property to "no".
In the report footer of the main report I wrote an expression:
=[febsubreport].[report]![febproduct1sales]
I have two problems I cannot figure out. 1) in the main report
footer,
it
is populating the product1sales results for the last
salesperson -
it
should
populate the sum of all of the sales people. The sub-report has
the
correct
calculation. 2) It is also populating the product1sales results
for
the
last
salesperson as the last field under each salesperson in the
detail
section
of
the report.
What am I doing wrong?
