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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
searching for similar records
Hello all, my database lists parts that have the following fields (amongst
others), BOM TIME PRICE CABLES CUSTOMER PARTNUMBER I would like to use a query that will return all records that are similar to a PARTNUMBER i enter. The query would search the records and return only the if the record has the following criteria; CUSTOMER is same as CUSTOMER in the PARTNUMBER i enter. BOM is within + or - 10% of the BOM on the PARTNUMBER i enter. TIME is within + or - 10% of the TIME on the PARTNUMBER i enter. PRICE is within + or - 10% of the PRICE on the PARTNUMBER i enter. CABLES is within + or - 10% of the CABLES on the PARTNUMBER i enter. I have managed in the past to enter each individual criteria (BOM,TIME Etc.) and return records but i am struggling to be able to enter a PARTNUMBER and have the records returned according to the above criteria. I would very much appreciate any help on this query. The query results will be used for a report. many thanks |
#2
|
|||
|
|||
searching for similar records
What type of fields are BOM, Time, Price, and cables?
What is your table Name? I have to assume that the fields that you are matching plus or minus 10% are numeric. By the way Time is NOT a good field name, since it is a function to return the current system time. Save the following as qPartMatch SELECT Customer, BOM, [Time] as TimeToDo, Price, Cables, PartNumber FROM SomeTable WHERE PartNumber = [What Part] Use the above nested query in another query. SELECT [SomeTable].BOM , [SomeTable].[Time] , [SomeTable].Price , [SomeTable].Cables , [SomeTable].Customer , [SomeTable].PartNumber FROM [SomeTable] INNER JOIN qPartMatch ON [SomeTable].Customer = qPartMatch.Customer AND [SomeTable].PartNumber = qPartMatch.PartNumber WHERE [SomeTable].BOM Between.9 * qPartMatch.BOM And 1.1 * qPartMatch.BOM AND [SomeTable].Price Between .9 * qPartMatch.Price and 1.1*qPartMatch.Price AND [SomeTable].Cables Between .9 qPartMatch.Cables and 1.1*qPartMatch.Cables AND [SomeTable].Cables Between .9 qPartMatch.TimeToDoand 1.1*qPartMatch.TimeToDo -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Maax" wrote in message ... Hello all, my database lists parts that have the following fields (amongst others), BOM TIME PRICE CABLES CUSTOMER PARTNUMBER I would like to use a query that will return all records that are similar to a PARTNUMBER i enter. The query would search the records and return only the if the record has the following criteria; CUSTOMER is same as CUSTOMER in the PARTNUMBER i enter. BOM is within + or - 10% of the BOM on the PARTNUMBER i enter. TIME is within + or - 10% of the TIME on the PARTNUMBER i enter. PRICE is within + or - 10% of the PRICE on the PARTNUMBER i enter. CABLES is within + or - 10% of the CABLES on the PARTNUMBER i enter. I have managed in the past to enter each individual criteria (BOM,TIME Etc.) and return records but i am struggling to be able to enter a PARTNUMBER and have the records returned according to the above criteria. I would very much appreciate any help on this query. The query results will be used for a report. many thanks |
#3
|
|||
|
|||
searching for similar records
Whoops. At least one Cut and paste error in there.
SELECT [SomeTable].BOM , [SomeTable].[Time] , [SomeTable].Price , [SomeTable].Cables , [SomeTable].Customer , [SomeTable].PartNumber FROM [SomeTable] INNER JOIN qPartMatch ON [SomeTable].Customer = qPartMatch.Customer AND [SomeTable].PartNumber = qPartMatch.PartNumber WHERE [SomeTable].BOM Between.9 * qPartMatch.BOM And 1.1 * qPartMatch.BOM AND [SomeTable].Price Between .9 * qPartMatch.Price and 1.1*qPartMatch.Price AND [SomeTable].Cables Between .9 qPartMatch.Cables and 1.1*qPartMatch.Cables AND [SomeTable].[Time] Between .9 qPartMatch.TimeToDoand 1.1*qPartMatch.TimeToDo -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "John Spencer" wrote in message ... What type of fields are BOM, Time, Price, and cables? What is your table Name? I have to assume that the fields that you are matching plus or minus 10% are numeric. By the way Time is NOT a good field name, since it is a function to return the current system time. Save the following as qPartMatch SELECT Customer, BOM, [Time] as TimeToDo, Price, Cables, PartNumber FROM SomeTable WHERE PartNumber = [What Part] Use the above nested query in another query. SELECT [SomeTable].BOM , [SomeTable].[Time] , [SomeTable].Price , [SomeTable].Cables , [SomeTable].Customer , [SomeTable].PartNumber FROM [SomeTable] INNER JOIN qPartMatch ON [SomeTable].Customer = qPartMatch.Customer AND [SomeTable].PartNumber = qPartMatch.PartNumber WHERE [SomeTable].BOM Between.9 * qPartMatch.BOM And 1.1 * qPartMatch.BOM AND [SomeTable].Price Between .9 * qPartMatch.Price and 1.1*qPartMatch.Price AND [SomeTable].Cables Between .9 qPartMatch.Cables and 1.1*qPartMatch.Cables AND [SomeTable].Cables Between .9 qPartMatch.TimeToDoand 1.1*qPartMatch.TimeToDo -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Maax" wrote in message ... Hello all, my database lists parts that have the following fields (amongst others), BOM TIME PRICE CABLES CUSTOMER PARTNUMBER I would like to use a query that will return all records that are similar to a PARTNUMBER i enter. The query would search the records and return only the if the record has the following criteria; CUSTOMER is same as CUSTOMER in the PARTNUMBER i enter. BOM is within + or - 10% of the BOM on the PARTNUMBER i enter. TIME is within + or - 10% of the TIME on the PARTNUMBER i enter. PRICE is within + or - 10% of the PRICE on the PARTNUMBER i enter. CABLES is within + or - 10% of the CABLES on the PARTNUMBER i enter. I have managed in the past to enter each individual criteria (BOM,TIME Etc.) and return records but i am struggling to be able to enter a PARTNUMBER and have the records returned according to the above criteria. I would very much appreciate any help on this query. The query results will be used for a report. many thanks |
#4
|
|||
|
|||
searching for similar records
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, Save the following as qPartMatch SELECT Customer, BOM, [Time] as TimeToDo, Price, Cables, PartNumber FROM SomeTable WHERE PartNumber = [What Part] You say use the above nested query in another query? How do i do this? Also the following part you supplied, SELECT [SomeTable].BOM , [SomeTable].[Time] , [SomeTable].Price , [SomeTable].Cables , [SomeTable].Customer , [SomeTable].PartNumber FROM [SomeTable] INNER JOIN qPartMatch ON [SomeTable].Customer = qPartMatch.Customer AND [SomeTable].PartNumber = qPartMatch.PartNumber WHERE [SomeTable].BOM Between.9 * qPartMatch.BOM And 1.1 * qPartMatch.BOM AND [SomeTable].Price Between .9 * qPartMatch.Price and 1.1*qPartMatch.Price AND [SomeTable].Cables Between .9 qPartMatch.Cables and 1.1*qPartMatch.Cables AND [SomeTable].Cables Between .9 qPartMatch.TimeToDoand 1.1*qPartMatch.TimeToDo The above, where you say "select" does this indicate the fields i need to use for the query? "sometable" is obviously the table i need to refer to where the fields are (Cables, BOM, Etc.). apologies for my lack of understanding. regards Max "John Spencer" wrote: What type of fields are BOM, Time, Price, and cables? What is your table Name? I have to assume that the fields that you are matching plus or minus 10% are numeric. By the way Time is NOT a good field name, since it is a function to return the current system time. Save the following as qPartMatch SELECT Customer, BOM, [Time] as TimeToDo, Price, Cables, PartNumber FROM SomeTable WHERE PartNumber = [What Part] Use the above nested query in another query. SELECT [SomeTable].BOM , [SomeTable].[Time] , [SomeTable].Price , [SomeTable].Cables , [SomeTable].Customer , [SomeTable].PartNumber FROM [SomeTable] INNER JOIN qPartMatch ON [SomeTable].Customer = qPartMatch.Customer AND [SomeTable].PartNumber = qPartMatch.PartNumber WHERE [SomeTable].BOM Between.9 * qPartMatch.BOM And 1.1 * qPartMatch.BOM AND [SomeTable].Price Between .9 * qPartMatch.Price and 1.1*qPartMatch.Price AND [SomeTable].Cables Between .9 qPartMatch.Cables and 1.1*qPartMatch.Cables AND [SomeTable].Cables Between .9 qPartMatch.TimeToDoand 1.1*qPartMatch.TimeToDo -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Maax" wrote in message ... Hello all, my database lists parts that have the following fields (amongst others), BOM TIME PRICE CABLES CUSTOMER PARTNUMBER I would like to use a query that will return all records that are similar to a PARTNUMBER i enter. The query would search the records and return only the if the record has the following criteria; CUSTOMER is same as CUSTOMER in the PARTNUMBER i enter. BOM is within + or - 10% of the BOM on the PARTNUMBER i enter. TIME is within + or - 10% of the TIME on the PARTNUMBER i enter. PRICE is within + or - 10% of the PRICE on the PARTNUMBER i enter. CABLES is within + or - 10% of the CABLES on the PARTNUMBER i enter. I have managed in the past to enter each individual criteria (BOM,TIME Etc.) and return records but i am struggling to be able to enter a PARTNUMBER and have the records returned according to the above criteria. I would very much appreciate any help on this query. The query results will be used for a report. many thanks |
#5
|
|||
|
|||
searching for similar records
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 |
#6
|
|||
|
|||
searching for similar records
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 |
#7
|
|||
|
|||
searching for similar records
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 |
#8
|
|||
|
|||
searching for similar records
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 |
#9
|
|||
|
|||
searching for similar records
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 |
#10
|
|||
|
|||
searching for similar records
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 | |
|
|