Access 2007 Report

[ftwguy]

I'm struggling with a report. I have 5 different .mdb files to pull data
from to make 1 report. I linked the tables, and tried to create a report but
the report only allows 1 record control source. That leaves out the date
from 4 tables. Basically, my report will have 1 customer listed with
customer ID, name, location. Below that will be the product purchased. All
5 mdb files have duplicate customers, but different products.

The Report ought to look like:

DFW123
ABC Company
123 Anywhere Street
Dallas, Texas 76210

Snack food 500 units
Soft Drink 300 units
Ice Cream 150 units
Milk 700 units
Water 600 units

Note: (the various units come from the 5 tables as record source, but Access
only allows one record source)

Presently, if I select tabel 1 as record source, then all I get is table 1
results and all the others show as an error.

I thought I'd try placing all the data info into 1 primary table, then
create a report. However, since I have necessary duplicates in the Customer
ID, then Access creates one report for each.

 

[Allen Browne]

By "duplicate customers", I'm not sure if you mean:
a) The same CustomerID number is used for different customers in the
different databases, or
b) The same CustomerID could be in different databases, and it will be the
same customer (names etc will always match), or
c) The same customer could appear in different databases, but they could
have different CustomerID numbers in the different databases.

Similary, you say the products are all different across the different
databases, but it's not clear if the ProductID values are unique. You will
certainly need to sort this out before you can proceed.

Once you have done that, it is possible to create a UNION query that
combines data from multiple tables, assuming that we are talking about the
same fields in the different tables. The process would be:

1. Import or link the tables from the different databases.
In Access 2007, click the External Database tab of the ribbon, and click
Access on the Import chunk.
In previous version, choose Get External on the File menu.

2. Create a query for each of these tables.
Make sure the fields are the same in each query, in the same order.
(No need to save these.)

3. Swtich the queries to SQL View (View menu), and copy the statements into
one query, with the words UNION ALL between them. Example:
SELECT Customer, Product, Quantity FROM Table1
UNION ALL
SELECT Customer, Product, Quantity FROM Table2
UNION ALL
SELECT Customer, Product, Quantity FROM Table3;

4. Save this query, and use it as the source for the report.

Access can't show you a UNION query in deisgn view, so make sure you have
them the way you want them before you combine them.

 

[ftwguy]

The answer is:
b) The same CustomerID could be in different databases, and it will be the
same customer (names etc will always match), (addresses), (city), (zip)
(contact name) etc will always match amongst the individual databases.

The MAJOR difference between all 5 of them is the PRODUCTS and the numeric
voume for each product sold.

Therefore, the "columns" from Customer ID through Zip Code are the SAME for
all five database tables. The "extra columns" of data for each are as
follows:
snack food, soft drink, ice cream, milk, water.

So I need 1 FINAL query or table to show:
customer ID, Store Name, Address, city, state, zip, snack food, soft drink,
ice cream, milk, water, as columns.

This would then give me EACH store with their sales of EACH product on an
individual row (record).

I will try to follow your instructions. I am confused as to how to make all
columns the same unless you mean to repeat snack food, soft drink, ice cream,
milk and water in each query when those fields are NOT in every table...or do
I need to rename those fields to "generic" titles in tables beofre I begin
the queries and call them product, quanity etc?

Sorry for not completely understanding the procedure.

 

[Allen Browne]

Oh dear; you have a non-normalized design, where the table has many columns
instead of many *records* in a related table (the way the Northwind sample
database has in the order details table.)

The best solution would be to normalize it. That is create a table of
products, and append the unique product names from each of the 5 source
databases. Then create the junction table betweeen clients and products.
This ClientProduct table will have fields like this:
ClientID relates to a client in the client table.
ProductID relates to a product in the Product table.
Quantity how many of this product this client bought.
ImportDate when this record was created

You would have to populate this junction table from each of the columns in
the main table, and then repeat the process for the other tables. It would
be possible to write code that did this.

I suspect the UNION query idea is going to be impractical. You will end up
with errors about too many fields. But if you want to proceed, you would
need to define all the possible products in each of the queries, even though
there were no records for them. You Null for the fields that don't apply,
e.g.:
SELECT [ClientID], [Snack Food], [ice cream], [milk], [water],
Null as MarsBars, Null As ChewingGum, ...
UNION ALL
SELECT [ClientID], Null AS [Snack Food], Null AS [ice cream],
[milk], [water], [MarsBars], [ChewingGum], ...
UNION ALL ...

 

[ftwguy]

Allen:
I had the tables originally broken out into Salesmen, Stores, Product, Order
tables and created front end forms for the staff to use. However, they
compalined as they were accustomed to using tables like an Excel
spreadsheet...all columns and rows to traverse. At least I got them to use
various tables for each of these 5 products product. Previously they had ONE
table with close to 200 columns, and 2200 rows....YES...440,000 cells.
It continually lcoked up due to size.

The complaint is now that we can't do the previous Access Reports that are
vital each quarter because I made the 5 separate tables for 5 products. So I
have to come up with a solution to make those reports.

Yes, there are still about 20 products left in the MAIN table which might be
down to 150 columns now since I removed 5 large volume products into their
own tables. But that has created this hudge report problems for me now.

Oh dear; you have a non-normalized design, where the table has many columns
instead of many *records* in a related table (the way the Northwind sample
database has in the order details table.)

The best solution would be to normalize it. That is create a table of
products, and append the unique product names from each of the 5 source
databases. Then create the junction table betweeen clients and products.
This ClientProduct table will have fields like this:
ClientID relates to a client in the client table.
ProductID relates to a product in the Product table.
Quantity how many of this product this client bought.
ImportDate when this record was created

You would have to populate this junction table from each of the columns in
the main table, and then repeat the process for the other tables. It would
be possible to write code that did this.

I suspect the UNION query idea is going to be impractical. You will end up
with errors about too many fields. But if you want to proceed, you would
need to define all the possible products in each of the queries, even though
there were no records for them. You Null for the fields that don't apply,
e.g.:
SELECT [ClientID], [Snack Food], [ice cream], [milk], [water],
Null as MarsBars, Null As ChewingGum, ...
UNION ALL
SELECT [ClientID], Null AS [Snack Food], Null AS [ice cream],
[milk], [water], [MarsBars], [ChewingGum], ...
UNION ALL ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The answer is:
b) The same CustomerID could be in different databases, and it will be the
same customer (names etc will always match), (addresses), (city), (zip)
(contact name) etc will always match amongst the individual databases.

The MAJOR difference between all 5 of them is the PRODUCTS and the numeric
voume for each product sold.

Therefore, the "columns" from Customer ID through Zip Code are the SAME
for
all five database tables. The "extra columns" of data for each are as
follows:
snack food, soft drink, ice cream, milk, water.

So I need 1 FINAL query or table to show:
customer ID, Store Name, Address, city, state, zip, snack food, soft
drink,
ice cream, milk, water, as columns.

This would then give me EACH store with their sales of EACH product on an
individual row (record).

I will try to follow your instructions. I am confused as to how to make
all
columns the same unless you mean to repeat snack food, soft drink, ice
cream,
milk and water in each query when those fields are NOT in every table...or
do
I need to rename those fields to "generic" titles in tables beofre I begin
the queries and call them product, quanity etc?

Sorry for not completely understanding the procedure.