Hi,
I'm wondering if someone can help with the following problem. I have
2 tables in an access database from which I would like to create a
third table.
They look like this:
table 1 table 2
======== ========
FNAME LNAME FNAME LNAME COLOR
Jack Smith Jack Smith Green
Jack Smith Jack Smith Blue
Jack Smith Jack Smith Red
Jack Smith Jack Smith Orange
John Orr John Orr Black
John Orr John Orr Brown
John Orr John Orr Blue
I'd like Access to perform a comparison and find the same FNAME and
LNAME
in tables above, remove any duplicates, and pull out the data from
the correct 'color' fields into one 'colorsummary' field as
illustrated below:
RESULT:
FNAME LNAME COLORSUMMARY
===== ===== =============
Jack Smith Green, Blue, Red, Orange
John Orr Black, Brown, Blue
Any suggestions on how to achieve this is much appreciated. I have
worked before with Access queries (and after much experimenting dont
seem to be getting far!) but obviously am missing the needed expertise
to make the above happen. Alternately, if you have other suggestions
of other software/languages to use which would be simpler (eg., excel
or some DB language) that would be appreciated.
Thanks
Jack 4 1224 ja******@lycos.co.uk (Jack) wrote in
news:69**************************@posting.google.c om: Hi,
I'm wondering if someone can help with the following problem. I have 2 tables in an access database from which I would like to create a third table.
They look like this:
table 1 table 2 ======== ======== FNAME LNAME FNAME LNAME COLOR Jack Smith Jack Smith Green Jack Smith Jack Smith Blue Jack Smith Jack Smith Red Jack Smith Jack Smith Orange John Orr John Orr Black John Orr John Orr Brown John Orr John Orr Blue
I'd like Access to perform a comparison and find the same FNAME and LNAME in tables above, remove any duplicates, and pull out the data from the correct 'color' fields into one 'colorsummary' field as illustrated below:
RESULT:
FNAME LNAME COLORSUMMARY ===== ===== ============= Jack Smith Green, Blue, Red, Orange John Orr Black, Brown, Blue
Any suggestions on how to achieve this is much appreciated. I have worked before with Access queries (and after much experimenting dont seem to be getting far!) but obviously am missing the needed expertise to make the above happen. Alternately, if you have other suggestions of other software/languages to use which would be simpler (eg., excel or some DB language) that would be appreciated.
Thanks Jack
Don't! Your plan violates the principle of atomicity; a row-column
intersection should hold just one value.
Read a little about
normalization,
relationships,
and
subforms/subreports.
You could end up with
something like:
People
UniqueID FName LName
1 Jack Smith
2 John Orr
Colours
UniqueID Color
1 Red
2 Orange
3 Yellow
4 Green
5 Blue
6 Purple
7 Brown
8 Black
PeopleWithColors
UniqueID PeopleID ColorID
1 1 1
2 1 2
3 1 4
4 1 5
5 2 5
6 2 7
7 2 8
While this may seem strange, as you work with it, you will find that it
<<<may>>> be the most efficient design that you can create.
Access Report and Form frameworks were created with this type of design in
mind, ... or they should have been.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Thanks Lyle for your quick reply
FNAME and LNAME below are just examples as the application I'm working
on is to have my database remove duplicate addresses for the purpose
of a mailing
At each address I'd like a summary field (like a memo field, freeform
text field) which shows the names of people at that same address:
for example, source data looks like this
name address
joe 2 main st
sue 6 oak blvd
tom 2 main st
jim 2 main st
art 6 oak blvd
and I want the resulting database to look like this
address names
2 main st joe, tom, jim
6 oak blvd sue, art
the reason for this is so I can treat each address as one record and
have the names summarized in one field so these can be presented in
one place on the merge letter I'm creating
Any help on how to do this appreciated
Thanks,Jack Don't! Your plan violates the principle of atomicity; a row-column intersection should hold just one value.
Read a little about normalization, relationships, and subforms/subreports.
Thanks Pieter - I will give that a try!
Jack pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>... This should do what you want:
http://www.mvps.org/access/modules/mdl0004.htm
Say John, Jack & Jill share the same address....
Your query result would look something like:
John, Jack, Jill Address City, State, Zip This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: John |
last post by:
Hi - I am trying to perform a simple append query, with no luck. I
have a table (MktPrices) that has the following fields: BondID,
PriceDate, Price. The objective is to allow the user to input a...
|
by: JMCN |
last post by:
hi
i have a general question regarding append queries in access 97. each
week i need to update my table(tblonlinereg) with new or modified
records. firstly, i import the text file into my...
|
by: Jack |
last post by:
Hi,
I'm wondering if someone can help with the following problem. I have
2 tables in an access database from which I would like to create a
third table.
They look like this:
table 1 ...
|
by: Clint Stowers |
last post by:
Running Ak2
Daily there is a report that is run where a summation of the detail records is
placed in the footer. Simple standard operation.
What I would like to do at the time the report is...
|
by: Daniel Wetzler |
last post by:
Dear MS SQL Experts,
I have to get the number of datasets within several tables in my MSSQL
2000 SP4 database.
Beyond these tables is one table with about 13 million entries.
If I perform a...
|
by: jpr |
last post by:
Friends, I would like some help with a code that allows me to run an
append query only if a specific field is not already stored into
another table.
Example.
I add a new customer to my database...
|
by: blackd77 |
last post by:
What I would LIKE to do is noted in the subject line. What I'm finding
is that "edit SQL" appears to only be an option if I am creating a
table. If I select "append to" the option to edit SQL...
|
by: Jason Lepack |
last post by:
I have a loop that loops through all records in all tables that have
"TSE" as the first letters. In that loop, based upon conditions of the
current record I have to add records to 1 of 5 different...
|
by: osward |
last post by:
Hi everyone,
I have query(s) that I need to perform which I couldn't figure out, and following is the situation
I have event table (which stores the event details) that has eid, date, time,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |