I got the concatenate function for Excel from Mike The Bike's earlier post (http://www.dbforums.com/showthread.php?t=1624671) ... simple cut and paste, study the formula, and save it for future studying. Now I want to do the same thing in Access:
The attached file is only a sample of my DB which has 60k records in it. You will notice that the "Address" values are broken up into 4 spearate fields:
Street Direction (North, South, etc.) --- some are blank
"Type" of street (Ave, Street, etc.) --- some of these are blank as well
There are a total of 17 tables in the DB. All have identical field names and (hopefully) all have unique records. There are 4 different fields that I needed to concatenate in order to get the street address to be in one field (felt pretty good figuring it out ). I did the following:
sorts are easily done.. thats why the data is stored in SQL. you can either set a sort by using a query, setting the sort programaticaly in a form
...when you select a specified button set the sort order to what ever column
me.orderbyon = true
perhaps even easier select a control on your form, and then select the A-Z or Z-A button
you can apply filters selct the column you want to filter on, and then button that looks like as funnel with a flash
so say you wanted to show records with a situs street of TUOLUMNE you'd find a record with TUOLUMNE in its situs street, then select the situs stret value and press the filter button... to remove a filter press the empty funnel button.
to select data form different tables requires a clear unambiguous method of associating data in table 1 with table 2. usually that is doen on whats called a primary and foreign key
a primary key is something that makes that record unique in that table, a foreign key is where that value is used in another table and is said to refer to the value in the other table.
say you had a table identifying types of property
100 grandiose pile (suitable for drug dealers and captains of industry)
and you had a table of properties
if you included a value from the property type of table in your property table then you could identify and associate similar types of property. so if a customer wanted to look at say 5 bed detached houses, you could request the database only return properties of type detached.
if you are just starting out on the db design trail can I suggest you have alook at this
Since all of the tables have the same fields, you could use a union query to join all of the tables together and then use a filter in your report or form to select records from any of the tables that meet one or more criteria that you would specify in your form (or form used to call the report).
For instance, if you had 1 table for condos, another for apartments, another for detached houses, you could easily produce a report of any unit that say had more than 4 bedrooms AND more than x square feet.
Well, I spent the better part of the day reading and playing with it. I took the LONG way around, but I got it done.
First I created a blank table with the structure only and appended the table with a "group" of the other tables. Then I did it two more times, making three "managable" tables, rather than trying to work with all the different ones.
Used the trim and concatenate functions to make the data correct, and then started filtering the data to achieve the results I wanted. It was time consuming but I enjoyed playing with it. I'm sure it could have been done in a matter of minutes but, what the heck... I had fun.
How does the union query work? What would the expresion look like?
I'm Old, I'm Grumpy, and I have no clue what I'm doing
Basically, a union query is used to access similar data from multiple tables so that a form or report can use the data from multiple tables as if the data was from 1 table. You write a separate query to return a result set from each table - from 5 tables requires 5 queries. Then you use the union query to combine the separate result sets into a single result set. Each of the 5 queries woud have to have the same number of fields with the same field names and the union query requires that you do it in SQL and would look something like the following:
Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO1
Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO2
UNION Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListO3
Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR1
Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR2
Union Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListR3
UNION Select street, unit, fulladdress, oname, Lphone, Ophone, Email, alpha from qPhListC1
ORDER BY street, unit;
You could also use union queries to break apart 1 record into multiple records for purposes of generating a report or form. In the above case, there were up to 3 owner names in each record in the owner table and up to 3 renter names in each record in the renter table and I wanted to produce a phone book report in which each owner/renter name would appear separately.
OK - So I'm just a little confused (Ignorance is not bliss. I understand some of it, but some is a bit fuzzy:
First, you said that the UQ (Union Query) pulls the data to be used in a form or report... can I do a "Make Table" from the query as well?
Second, I am assuming the SQL statement above all one UQ, and not multiple Union Queries (???):
I noticed that the first part of the statement begins with "select", whereas the balance of it begins with "Union Select" ---- would like to understand the progression as well as how the results are achieved... I want to understand why the beginning starts with select, the following statements start with Union Select, and what trasnpires when the query is run.
Did you first do independant queries on each of the tables, and then do a UQ based on those queries?
I'm Old, I'm Grumpy, and I have no clue what I'm doing
The syntax is correct. The first Select returns a result set as usual and the subsequent UNION SELECT statements add to the first result set. Each of the Select or Union Select statements in the example references separate queries or tables.
Yes, you could use it to make a new table. Otherwise, if there was a reason to keep the data in separate tables, you could use it to 'work' as all of the data was in one table.