Question

  • Thread starter Thread starter texasman
  • Start date Start date
T

texasman

Since this is the first time I have created an Access database I'm sure
this is a stupid question but here goes....I have created two different
tables. One with Customer information in it and the other with Product
information in it. I'd like to be able to create a report based on 2
different queries (one that asks for the customer name and the next ask
for the product id). Is it possible to create this report without any
fields being linked between the two tables. I'm using Access 2000 (just
ordered 2003). Any help is greatly appreciated.
 
The short answer is you a report may only have one
recordsource, whether that is one table or one query that
holds the info from many related tables.

You haven't given any details, but the fact that you only
have 2 tables, Customers and Products, makes me question if
your design is optimized. Usually a db with Customers and
Products would also have an Order table and a OrderDetails
table to hold the different products the customer ordered on
that order.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
What I am trying to do is convert and old foxpro database for someone.
There were only two different .dfb files that held the information.
What I did was use a utility to export the data from each of those .dbf
files and then imported them into two different tables with the same
field names. Now I'm trying to reproduce reports similar to the old
database. What they had before was the ability to query for a certain
customer, then query for a heat number (which is a product
identification for them). Once they had selected a customer and then
selected the heat it would print a report with the customer name and
address information as well as the heat number and Chemical Analysis
for that heat. I guess where I'm going wrong is writing two different
queries. The problem I was having was when I was creating my queries
it would tell me that I was trying to create from two different tables
that had no relationships. It would tell me I needed to close the
query and go to relationships. The problem is that there is NO
relationship between these two tables. Any help you can give me?
 
Ok I have two tables:
Customers (with the following fields):
Customer Name
Attn:
Address
City
State
Zip

Heat Data (with the following fields):
Heat Number
Lot Number
Diameter
Wall
Flatten
Hydro
C
MN
PH
Su
S
CU
N
CR
MO
VA
Yield
Tensile
Elong %

All I want is to be able to do is print a report that has the customer
name and the heat information. What they would do ideally is query fo
the customer (would be nice to have a drop down to find the customer
then they would choose the heat number (there are 12,000 differen
heats). Based on the information given from the query I would like t
create a report that prints out the chemical analysis in those field
under the heat table. Is this possible??
 
Well, as you say, there is no apparant relationship between
these tables so it looks like you can't do the reporting
that you want as there is no way to pull a relationship out
of thin air. You can get your chemical analysis at this
point, but I see no way to relate it to a Customer.

I am not a FoxPro expert, but I think that at least some
versions used 'index' files in addition to the .dbf files,
or that's what I recall when I did some linking to them in
the past. Are there any files in the original program that
have extensions of *.idx or *.cdx? If there are, it is
possible that these could be key to the problem.

Also, you mention that you used a third party utility to
export the data from the .dbf files. Have you tried copying
the files intact and linking directly from Access using
File/GetExternalData/LinkTables? It is possible that you
will get a prompt for an associated index file and at the
least you will have a direct peek into the unchanged table.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
The problem I had originally (and the reason I exported) was that when I
would pick the DBF file that I wanted to import or link it would ask me
for the associated index file. There are .cdx files. I would point it
to the corresponding .cdx file but it would always tell me that the
index was not found.
 
You may want to try that again. I recall something similar
happening, but things working fine even after the error
message. I think that this is pretty key to your problem.

You also may want to do a new post on this problem on the
external data group microsoft.public.access.externaldata.
There may be some folks there that have good experience with
the FoxPro issues.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
I still haven't solved anything but I do appreciate your posts. I've
found some documentation on Microsofts site that talks about using ODBC
to import the FoxPro files. It works on 1 of the tables but not on the
second (I get an error message and it just stops). I'm still trying
though. Thanks for your posts Gary.
 
Well I was able to get Access to link the tables and I'm looking at the
from inside access. I still see no link between the tables. An
suggestions
 
The problem is that I see no common fields between all three in which to
build a relationship. Maybe I should just give up. I knew when I
offered to help it wouldn't be easy :))
 
Whoops, now you are talking about three tables. What is the
third one?

I would still suggest another post as well to the
externaldata group. I did see a couple of posts from a
FoxPro MVP there.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Thanks for all of your help. What I have done (and it actually seems t
be working pretty well) is created a new field in each table. I jus
gave it a generic name and have set the value the same "YES" for ever
single record. I then went in and made a relationship between the tw
fields. I created a query and it allowed me to do it. I am then jus
prompting for the Customer Name and also the Heat Number. It seems t
be pulling up the info I want. Looks like I might be ready to create
report. Just a little more testing. Thanks a lot
 
texasman said:
Since this is the first time I have created an Access database I'm sure
this is a stupid question but here goes....I have created two different
tables. One with Customer information in it and the other with Product
information in it. I'd like to be able to create a report based on 2
different queries (one that asks for the customer name and the next ask
for the product id). Is it possible to create this report without any
fields being linked between the two tables. I'm using Access 2000 (just
ordered 2003). Any help is greatly appreciated.
Click to expand...

Here's a little different guess at what they might have done
before. You could create a main report just for the
customer data along with a subreport for the heat data. The
record source query for the main report would use a
parameter in its Where clause to filter the data to just the
customer specified on your form (or popup prompt). The
subreport's record source query would use a similar
parameter to filter the heat subreport to the specified
heat.

With this kind or arrangement, the relationship between the
two tables is entirely in the mind of the user requesting
the report.
 
Back
Top