Re: Access 2007 Report

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.

--
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: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: Help ... Creating an invoice
    ... So what you will want is a query that will return the required information ... for a report to use. ... for just the records for that customer. ... > if I want that order to be placed on the invoice? ...
    (microsoft.public.access.forms)
  • Re: Help ... Creating an invoice
    ... That query should use the customerID value to filter> for just the records for that customer. ... >> if I want that order to be placed on the invoice? ... My original answer was that you would> automatically generate a report including all orders for that customer. ...
    (microsoft.public.access.forms)
  • Re: Report / Sub-Report Problerm
    ... the customer account in the mainform, so equipment that does not match the ... It might be easier to do this with just one report instead of a report and ... Create a query using just tblCustomers and tblProductGenerics. ... this combination of customer and product exists in the tblCustomerEquipment ...
    (microsoft.public.access.reports)
  • Re: move data from list box or combo box to another list
    ... employees is because each employee gets paid commission on the service they ... The customer and dates are ... Lets address your problem with your report which seems to be your main ... Oh yeah and also you should/could have a filter in your query so that your ...
    (microsoft.public.access.forms)
  • 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)