About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question I tried to create this in Excel and the functionality was limited and now I am thinking that Access is the right way to go.
My goal is to input a contacts name, their need, and what product or service they sell. From that database, I would like a report on all the contacts that match up. By match up I mean if jane wants a new hair dresser and brenda is a hair dresser. The worksheet would show Jane, her need, brenda, her service.
I need the report to show multiple matches. Say Kim needed a hair dresser as well. I would like the worksheet to show Jane, needs a hair dresser, brenda,is a hair
dresser
then on the next row
Kim, needs a hair dresser, brenda, is a hair dresser
My other problem is to input the data in way where the access does not have to be intuitive to create matches.
Ex. John wants investment help and jake is a wealth manager
I need access to make a match with the above two.
I thought a way I could fix this was to have a needs category column and a needs details column
and then have a product/service category column and a product/service details column
with the above data you could get the following output
John, finance, investment help, jake , finance, wealth manager
then I could review the matches and erase all the ones that are not good matches
when I am inputting the category it would be great if I could pick from a drop down list of previously inputted categories. If none of the categories fit then there is an option to put a new category in and now the new category is on the drop down list.
I am open to all options especially if you have a simpler way of achieving the results. I may be making it more complicated than it needs to be.
thank you
Answer Hi Mark,
Too many general questions here to be specific.
I suggest you have a table of people, a table of skills, then an associative table of PersonID and SkillID that associates Person and Skill
You can then write queries such as, the one below which will show you all the people and what skills they have, grouped by skill:-
SELECT Nz(a.Skill,"No Skill") As PersonSkill, b.Name
FROM (tblSkill As a INNER JOIN tblSkillXPerson as x
ON a.SkillID = x.SkillID) RIGHT OUTER JOIN tblPerson as b
ON x.PersonID = b.PersonID
ORDER BY Nz(a.Skill,"No Skill") As PersonSkill, b.Name;
in the above my tables are defined as:-
tblPerson
PersonID = PK autonumber
Name
tblSkill
SkillID = PK autonumber
Skill
tblSkillXPerson
SkillID = PK Numeric LONG - FK on tblSkill
PersonID = PK Numeric LONG - FK on tblPerson
