A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

searching for similar records



 
 
Thread Tools Display Modes
  #11  
Old February 20th, 2007, 01:24 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default searching for similar records

Glad it's working for you.

I wish I had understood better from the beginning what you were trying to
accomplish. I should have paid closer attention to your first posting.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
yes, thats exactly what i want. i have dropped the join on the part number
and it works perfectly now. Many thanks for your patience and help.
It is very much appreciated.
its now 4.30am in the UK so i can perhaps get a couple of hours sleep
before
work.

Thanks again.



"John Spencer" wrote:

Try dropping the join on the part number also. I must admit, I am a bit
baffled on exactly what you want returned.

After reviewing this thread, it seems that you want all parts bought by
a customers (or customers) that have bought a particular part as long as
the parts are within 10 percent of the cost and the cable and the bom
and the time.

So if I bought part no 52, what other parts did I buy that were within
10 percent of the cost and the bom and the time and the cable. Whatever
those are. If that is correct then you probably only want to join on
the customer and use criteria on the other items. You don't want to do
anything at all with criteria or joins on partnumber.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Maax wrote:
John, i removed link but still the same. i have tried taking out all of
the
between criteria and replacing the price criteria with a between 5 and
200.
This should return many records but still only returns the record that
has
the same part number i enter for the search.

I will continue to play around with it but its a little beyond me i am
afraid.

many thanks for your help

"John Spencer" wrote:

No, you do not want to link by the primary key field. That is
probably what
is restricting your results to one record. REmove that link by
right-clicking on it and selecting delete.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
Hi John, i am following your instructions (i think) but when i run
the
query
the only record returned is the one that i entered as the part
number. It
is
not returning other parts that are within the criteria.
I will have a play with it and hopefully something will click into
place.
One question i do have, should only the partnumber and customer be
linked?
These are linked but also the unique ID(key) from the table is
linked.

regards Max



"John Spencer" wrote:

It sounds as if you are using the query design view (the query grid)
to
build your queries.

Open a new query
-Select your table
-Click close
-Add the fields to the query grid
-under part number enter
= [What Part]

Save this query as qPartMatch.

Open a new query
-- Select your table
-- Click on the query tab
-- Select the saved query
-- Click close
-- if there is no join line between partNumber and PartNumber Drag
from
the
table to the query
-- if there is no join line between Customer and customer drag again
-- Add the fields you want from the table
-- in the criteria under the relevant fields you will have to type
BETWEEN [qPartMatch].[BOM] *.9 And [qPartMatch].[BOM]*1.1

Alternative is to copy the proposed SQL statements into the SQL view
of
the
query and edit the table (and field) names. Save the first as
noted.

Now copy the second one as posted and change the table name again.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
Hi John, many thanks for reply.
I have a minor problem, i am very novice and although i can create
basic
queries i am unsure where to enter the following,

SNIP








  #12  
Old February 20th, 2007, 01:38 PM posted to microsoft.public.access.queries
Maax
external usenet poster
 
Posts: 63
Default searching for similar records

actually when it does not work first time it enables me to learn a little
more. I can go through it locigally and see why it did not work. Its all
useful stuff for future problems which i will encounter i am sure.

thanks again

"John Spencer" wrote:

Glad it's working for you.

I wish I had understood better from the beginning what you were trying to
accomplish. I should have paid closer attention to your first posting.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
yes, thats exactly what i want. i have dropped the join on the part number
and it works perfectly now. Many thanks for your patience and help.
It is very much appreciated.
its now 4.30am in the UK so i can perhaps get a couple of hours sleep
before
work.

Thanks again.



"John Spencer" wrote:

Try dropping the join on the part number also. I must admit, I am a bit
baffled on exactly what you want returned.

After reviewing this thread, it seems that you want all parts bought by
a customers (or customers) that have bought a particular part as long as
the parts are within 10 percent of the cost and the cable and the bom
and the time.

So if I bought part no 52, what other parts did I buy that were within
10 percent of the cost and the bom and the time and the cable. Whatever
those are. If that is correct then you probably only want to join on
the customer and use criteria on the other items. You don't want to do
anything at all with criteria or joins on partnumber.



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Maax wrote:
John, i removed link but still the same. i have tried taking out all of
the
between criteria and replacing the price criteria with a between 5 and
200.
This should return many records but still only returns the record that
has
the same part number i enter for the search.

I will continue to play around with it but its a little beyond me i am
afraid.

many thanks for your help

"John Spencer" wrote:

No, you do not want to link by the primary key field. That is
probably what
is restricting your results to one record. REmove that link by
right-clicking on it and selecting delete.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
Hi John, i am following your instructions (i think) but when i run
the
query
the only record returned is the one that i entered as the part
number. It
is
not returning other parts that are within the criteria.
I will have a play with it and hopefully something will click into
place.
One question i do have, should only the partnumber and customer be
linked?
These are linked but also the unique ID(key) from the table is
linked.

regards Max



"John Spencer" wrote:

It sounds as if you are using the query design view (the query grid)
to
build your queries.

Open a new query
-Select your table
-Click close
-Add the fields to the query grid
-under part number enter
= [What Part]

Save this query as qPartMatch.

Open a new query
-- Select your table
-- Click on the query tab
-- Select the saved query
-- Click close
-- if there is no join line between partNumber and PartNumber Drag
from
the
table to the query
-- if there is no join line between Customer and customer drag again
-- Add the fields you want from the table
-- in the criteria under the relevant fields you will have to type
BETWEEN [qPartMatch].[BOM] *.9 And [qPartMatch].[BOM]*1.1

Alternative is to copy the proposed SQL statements into the SQL view
of
the
query and edit the table (and field) names. Save the first as
noted.

Now copy the second one as posted and change the table name again.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Maax" wrote in message
...
Hi John, many thanks for reply.
I have a minor problem, i am very novice and although i can create
basic
queries i am unsure where to enter the following,

SNIP









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.