Re: Access 2007 Report

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ftwguy" <danno467@xxxxxxxxxxxxxxxx> wrote in message
news:E90A2994-CAF0-4F91-B690-5FAEF7F0FE7E@xxxxxxxxxxxxxxxx
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" wrote:

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" <danno467@xxxxxxxxxxxxxxxx> wrote in message
news:97D769F6-585B-46D3-887D-0A3B4595C9C6@xxxxxxxxxxxxxxxx
> 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.

.

Relevant Pages

  • Re: blank lines in the address block
    ... I have compared everything from 'soup to nuts' between both databases. ... Northwind sample report and paste them into MY report and then changing the ... > Customer Labels report. ... >> Dave French ...
    (microsoft.public.access.reports)
  • Re: Any way of speeding up a very large query?
    ... "Cannot open any more databases. ... This only occurs when I run the report, go into design view, run the report, ... I noticed That this may be due to the amount of fields in the query that the ... diagnostic test run is one of the codes in the test codes table who is in ...
    (microsoft.public.access.queries)
  • Re: Access 2007 Report
    ... etc will always match amongst the individual databases. ... the "columns" from Customer ID through Zip Code are the SAME for ... So I need 1 FINAL query or table to show: ... and use it as the source for the report. ...
    (microsoft.public.access.reports)
  • Re: Access 2007 Report
    ... I suspect the UNION query idea is going to be impractical. ... The same CustomerID could be in different databases, ... the "columns" from Customer ID through Zip Code are the SAME ... and use it as the source for the report. ...
    (microsoft.public.access.reports)
  • Re: starting freelance
    ... In the past I just rebuilt the databases fresh like you ... Maybe you have advice on how to estimate time spent for creating the ... Of course your client wants a price. ... You can end up rebuilding structures over and over because the customer "forgot" to tell you about some huge exception or odd rule until the day before you go live. ...
    (comp.databases.filemaker)