How do I combine various tables?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a mailing list that I am trying to create for a fund raising event.
The tables I have created from previous years have duplicate entries. I used
a union query to combine the tables, but it keeps asking for parameters in
the name and postal code fields. I wantr to remove the parameters but can't.
Help!!
 
It's the holiday season so please be in a giving mood concerning your
current SQL of the union query as well as your existing table structures.
 
Duane,

I copied the tutorial that was given from the help menu as follows:

Select [First Name], [Last Name], [Address], [City], [State], [Postal Code]
From [1999]
Where [State]="NY"

Union All Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2000]
Where [State]="NY"

UNION ALL Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2002]
Where [State]="NY";

NY is a constant and I wanted all available entries to show up, and
eliminate the duplicates. The problem is when I run the query it asks for
parameters in the first and last name fields as well as the postal code
field. I need to mail these things so the first and last name and postal
codes are a little important. By not entering anything in the parameter
boxes Access is removing all of that information.

Did I give enough?

Keith
 
Apparently you don't have fields with the names you are being prompted for.
Have you looked at the field names in design view to see what the really
are? I expect you have used the Caption property for your fields. I don't
ever use caption properties.

Also, do you actually have tables for each year or is this a tutorial
example?

--
Duane Hookom
MS Access MVP


Keith Hocky said:
Duane,

I copied the tutorial that was given from the help menu as follows:

Select [First Name], [Last Name], [Address], [City], [State], [Postal
Code]
From [1999]
Where [State]="NY"

Union All Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2000]
Where [State]="NY"

UNION ALL Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2002]
Where [State]="NY";

NY is a constant and I wanted all available entries to show up, and
eliminate the duplicates. The problem is when I run the query it asks for
parameters in the first and last name fields as well as the postal code
field. I need to mail these things so the first and last name and postal
codes are a little important. By not entering anything in the parameter
boxes Access is removing all of that information.

Did I give enough?

Keith


Duane Hookom said:
It's the holiday season so please be in a giving mood concerning your
current SQL of the union query as well as your existing table structures.
Click to expand...
Click to expand...
 
Duane,

I copied the tutorial that was given from the help menu as follows:

Select [First Name], [Last Name], [Address], [City], [State], [Postal Code]
From [1999]
Where [State]="NY"

Union All Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2000]
Where [State]="NY"

UNION ALL Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2002]
Where [State]="NY";
Click to expand...

Then whoever wrote your tutorial is (unless they're using this as a
Horrible Example and explaining how to redo it correctly) very inept.
Storing data in tablenames is Extremely Bad Design. I'll *hope* that
this is an example of a Normalizing Union Query to create a properly
normalized table... but given the criteria, I fear it isn't. :-{(
NY is a constant and I wanted all available entries to show up, and
eliminate the duplicates. The problem is when I run the query it asks for
parameters in the first and last name fields as well as the postal code
field. I need to mail these things so the first and last name and postal
codes are a little important. By not entering anything in the parameter
boxes Access is removing all of that information.
Click to expand...

If you're getting prompts for [First Name], I must presume that the
table named [1999] (or one or both of the other tables) has no field
with that name. Open the tables in design view and check - do they
have fields with the names for which you're being prompted? If they
do, are they Lookup fields (which would mean that they do not actually
contain the data that they show)?

John W. Vinson[MVP]
 
Gentlemen,

The tutorial, or example, came right out of the help menu.

A little more information:

Each table (1999, 2000, 2002) is created for a seperate event (a Tool Raffle
for the fire comapny I belong to). I use it to keep track of ticket #'s,
Names (First and Last, each in a seperate field), Address and zip (postal)
codes, phone numbers, and who sold the tickets. I usually use a form to
enter this data.

If you want to see more information on what the tool raffle is go to
www.akronfireco.com Want a ticket?

When I get home, I'll check that these fields are named correctly (I think
they are as they are the default names for a mailing list table).

Thanks, Keith


John Vinson said:
Duane,

I copied the tutorial that was given from the help menu as follows:

Select [First Name], [Last Name], [Address], [City], [State], [Postal Code]
From [1999]
Where [State]="NY"

Union All Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2000]
Where [State]="NY"

UNION ALL Select [First Name], [Last Name], [Address], [City], [State],
[Postal Code]
From [2002]
Where [State]="NY";
Click to expand...

Then whoever wrote your tutorial is (unless they're using this as a
Horrible Example and explaining how to redo it correctly) very inept.
Storing data in tablenames is Extremely Bad Design. I'll *hope* that
this is an example of a Normalizing Union Query to create a properly
normalized table... but given the criteria, I fear it isn't. :-{(
NY is a constant and I wanted all available entries to show up, and
eliminate the duplicates. The problem is when I run the query it asks for
parameters in the first and last name fields as well as the postal code
field. I need to mail these things so the first and last name and postal
codes are a little important. By not entering anything in the parameter
boxes Access is removing all of that information.
Click to expand...

If you're getting prompts for [First Name], I must presume that the
table named [1999] (or one or both of the other tables) has no field
with that name. Open the tables in design view and check - do they
have fields with the names for which you're being prompted? If they
do, are they Lookup fields (which would mean that they do not actually
contain the data that they show)?

John W. Vinson[MVP]
Click to expand...
 
Each table (1999, 2000, 2002) is created for a seperate event (a Tool Raffle
for the fire comapny I belong to). I use it to keep track of ticket #'s,
Names (First and Last, each in a seperate field), Address and zip (postal)
codes, phone numbers, and who sold the tickets. I usually use a form to
enter this data.
Click to expand...

I'd again strongly suggest storing all this data in *one* table, with
a RaffleYear field to distinguish the 1999 raffle from the 2004
raffle. Storing data in tablenames is certain to get you in trouble.
If you want to see more information on what the tool raffle is go to
www.akronfireco.com Want a ticket?
Click to expand...

<g> No thanks...

John W. Vinson[MVP]
 
Back
Top