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
|
|||
|
|||
Concatenatd fields in a query for a searching form
Dear All,
I have a Data Entry Form which have several fields, five of them are Sample1, Sample2, Sample3, Sample4, Sample5. They are separeted because each sample have different Custom Tariff Number. I built also a second form, used for tracking (findind) the samples sent, in this form I have a field for [Internal Code], [Customer] and [Country]. I would like to improve this form inserting a sample field, as well. My question is: in a query can I concatenate the five samples fields in one column only, such as: Sample: [Sample1] & " " & [Sample2] & " " & [Sample3] & " " & [Sample4] & " " & [Sample5] having the criteria row the following statement: Like ("*" & [Forms]![frmFind]![Sample] & "*") I made some trials, but had negative results. Thank you very much for your help, Marc |
#2
|
|||
|
|||
Marc
I have no idea what "negative results" you got ... it would be helpful in diagnosing what didn't work. If you have multiple fields (by the way, tables have fields, forms have controls, possibly bound to table fields) in your underlying table, with names like "Sample1", "Sample2", ..., there's a good chance your data design would benefit from further normalization. The use of repeating field names like that is fairly common ... in spreadsheets! Access is a relational database, and you don't want to store data the same way you would in a spreadsheet. "Why not?", you ask? For one thing, it makes doing the kind of query you've posed much more difficult! Even if each sample has an associated CustomTariffNumber, you can create a table something like: tblWhateverYouAreWorkingWith WhateverID Sample (whatever about the sample you are measuring) CustomTariffNumber With a design similar to this, you only look one place for a sample. -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ... Marc I have no idea what "negative results" you got ... it would be helpful in diagnosing what didn't work. If you have multiple fields (by the way, tables have fields, forms have controls, possibly bound to table fields) in your underlying table, with names like "Sample1", "Sample2", ..., there's a good chance your data design would benefit from further normalization. The use of repeating field names like that is fairly common ... in spreadsheets! Access is a relational database, and you don't want to store data the same way you would in a spreadsheet. "Why not?", you ask? For one thing, it makes doing the kind of query you've posed much more difficult! Even if each sample has an associated CustomTariffNumber, you can create a table something like: tblWhateverYouAreWorkingWith WhateverID Sample (whatever about the sample you are measuring) CustomTariffNumber With a design similar to this, you only look one place for a sample. -- Good luck Jeff Boyce Access MVP Thank you for you prompt answer Mr. Boyce, I built the database day after day starting from a draft, trying to improve it in every point. You are right, when you say it needs a further normalization, because the samples is the last section to adjust. The data entry table is the main table, but there are also the Continent, Country, Customer, Packing and Courrier tables. In the Data Entry Form, with a combobox I select the continent, then in a second one I can choose only the countries present in that continet, afterwards in the third combobox I can see only the customers present in that country. Selecting the customer the address autofills in dedicated controlls and so on. We have more than 400 products and building a right database, I should store them in a table named Samples, like you also suggested me. Sometimes customers requires more than one product, so in the data entry form (also table) I need about 5 sample fields with its custom tariff number field and I do not know how to solve this point, taking also into consideration that the final user unfortunately is not disposed for the computer, least of all access. However, solving this problem, how can I concatenate more fields in only a column in a query in order to use it in a form for tracking the samples sent? Any advices are welcomed. Kind regards, Marc |
#4
|
|||
|
|||
Marc
Please re-read my response. I should have been more explicit in recommending that you modify your data structure BEFORE trying to find a way to display it in a form, or to query it. From your description, I still am having trouble visualizing your table structure. In a relational database, everything starts with the data (i.e., table structure). Everything else (forms, queries, reports, ...) are based on that structure. If you provide more information about the structure, the 'group readers may be able to offer additional ideas/suggestions. -- More info, please ... Jeff Boyce Access MVP |
#5
|
|||
|
|||
[Cut]
Jeff, thank you in ansering me and for you frankness. The database is composed with 6 tables. In the satellite tables (continent, country, customer, packing, courrier) I insert *only one time* all the data available and then in the DataEntry Form these data are filtered and recalled by comboboxes. The tables a [tblContinent] idContinent - text (primary key) Continent - text [tblCountry] idcountry - text (primary key) country -text eu (europeaunion) - boolean field [tblCustomer] idcustomer - counter (primary key) customer - text address1 - text address2 - text address3 - text address4 - text fao - text [tblPacking] IdPacking - counter (primary key) Packing - text [tblCourrier] IdCourrier - Counter (primary key) Courrier - - text Main Table [tblDataEntry] ProformaInvoiceN° - Text (Counter and Primary Key) ShippingDate - Date/hour OurCode - Text Reference - Text Courrier - Text AirWayBill - Text N°Packing - Text Packing - Text GrossWeight - Text NetWeight - Text Dimension - Text Cost - Text Sample1 - Text CustomTariffN°1 - Text Sample2 - Text CustomTariffN°2 - Text Sample3 - Text CustomTariffN°3 - Text Sample4 - Text CustomTariffN°4 - Text Sample5 - Text CustomTariffN°5 - Text Continent - Text Country - Text EU (EuropeanUnion) - Text Customer - Text Address1 - Text Address2 - Text Address3 - Text Address4 - Text Fao - Text Hope this can be usefull for you. Thank in advance and kind regards, Marc |
#6
|
|||
|
|||
Marc
I'll reiterate my earlier suggestion. Your design with repeating fields (field groups - SampleN, CustomTariffN) is not well normalized. Removing this information to a "child" table would allow you to better use Access' functions and features. One of the clues to the "one-to-many" nature of this relationship is in your post wherein you stated that a customer (one) could have more than one sample, with its related CustomTariff (many). I also noted that you have a table with Customer information (apparently name, address and fao), but that you also repeat those fields in your tblDataEntry. Again, considering relational database design, this redundant data storage is both unnecessary and it creates a situation in which you must ensure the synchronization of the data between the two tables. If you keep this design, you will need to handle the situation in which a name/address/fao of a customer is changed in one table, but not the other -- now, which one is correct?! (the better design has this information recorded only once, making that one instance the correct version) I also noted that your tblDataEntry held NONE of the primary keys from the related tables, only their text values. This presents the same issue discussed above for the customer. If you wish to further normalize your data structure, consider reading more on the topic. The reason for having the related tables is so that you can store the ID of the related table's value in your "one" table, not the text of that related table. -- Good luck Jeff Boyce Access MVP |
#7
|
|||
|
|||
"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ... Marc I'll reiterate my earlier suggestion. Your design with repeating fields (field groups - SampleN, CustomTariffN) is not well normalized. Removing this information to a "child" table would allow you to better use Access' functions and features. One of the clues to the "one-to-many" nature of this relationship is in your post wherein you stated that a customer (one) could have more than one sample, with its related CustomTariff (many). I also noted that you have a table with Customer information (apparently name, address and fao), but that you also repeat those fields in your tblDataEntry. Again, considering relational database design, this redundant data storage is both unnecessary and it creates a situation in which you must ensure the synchronization of the data between the two tables. If you keep this design, you will need to handle the situation in which a name/address/fao of a customer is changed in one table, but not the other -- now, which one is correct?! (the better design has this information recorded only once, making that one instance the correct version) I also noted that your tblDataEntry held NONE of the primary keys from the related tables, only their text values. This presents the same issue discussed above for the customer. If you wish to further normalize your data structure, consider reading more on the topic. The reason for having the related tables is so that you can store the ID of the related table's value in your "one" table, not the text of that related table. -- Good luck Jeff Boyce Access MVP Thank you for your advices Jeff, I will try to focus how better normalize the tables, since I like the perfect things. Nevertheless, there is still something that I don't understand. If I make a related Customers table with a Samples one, how can I manage all this in a form? Using a subform? And all the other fields, such Shipping Date, Continent, Country, Courrier, AWB, Reference, GrossWeight, and so on .... using an other subform? I am focusing a form with 2 subforms, but I am not yet sure how to design all this. Moreover, I would like to have a whole simple form, since the final user has no aptitude for the computer and I am afraid that all this would be too complicated for she. Anyway, thank you again for your interest. Kind regards Marc |
#8
|
|||
|
|||
Marc
(see in-line comments) Thank you for your advices Jeff, I will try to focus how better normalize the tables, since I like the perfect things. Nevertheless, there is still something that I don't understand. If I make a related Customers table with a Samples one, how can I manage all this in a form? Using a subform? If you have a customer with multiple samples, a common approach is to put the customer information in a main form, and the related samples information in a subform. And all the other fields, such Shipping Date, Continent, Country, Courrier, AWB, Reference, GrossWeight, and so on ... using an other subform? If I understand your data structure, there are only ONE of each of these per customer, so you wouldn't need to use a subform. These data elements, based on your customer table design, only happen once. There is one thing that I'm still puzzled about, however. Based on your design, it appears a given customer has one and only one ... ?Order? I can't tell how the samples relate, but I'm wondering if you are dealing with a situation analogous to one with multiple customers, and each customer can have multiple orders, and each customer's order can have multiple ... ?samples? -- Good luck Jeff Boyce Access MVP |
#9
|
|||
|
|||
"Jeff Boyce" -DISCARD_HYPHEN_TO_END ha scritto nel
messaggio ... Marc (see in-line comments) Thank you for your advices Jeff, I will try to focus how better normalize the tables, since I like the perfect things. Nevertheless, there is still something that I don't understand. If I make a related Customers table with a Samples one, how can I manage all this in a form? Using a subform? If you have a customer with multiple samples, a common approach is to put the customer information in a main form, and the related samples information in a subform. And all the other fields, such Shipping Date, Continent, Country, Courrier, AWB, Reference, GrossWeight, and so on ... using an other subform? If I understand your data structure, there are only ONE of each of these per customer, so you wouldn't need to use a subform. These data elements, based on your customer table design, only happen once. There is one thing that I'm still puzzled about, however. Based on your design, it appears a given customer has one and only one ... ?Order? I can't tell how the samples relate, but I'm wondering if you are dealing with a situation analogous to one with multiple customers, and each customer can have multiple orders, and each customer's order can have multiple ... ?samples? -- Good luck Jeff Boyce Access MVP Thank you Jeff for your answer, I explored the Northwind database and I saw something of similar to my realty, even if it doesn't completely fit it. Do you mind if I send you my masterpiece, for having a good knowledge of my mistake? I should like to solve this problem, because it could be a good experience, since I can learn from my mistakes, but you should only explore my database not adjust it. As to your questions, for instance, this week, a customer can ask some samples and next month he can require further different ones, so I should have a relation one (customer) to many (samples) .... Let me know something if you are interested in looking at my database. Kind regards, Marc |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Text form fields | Jerry | General Discussion | 5 | August 24th, 2004 10:43 PM |
insert query with fields from a form | doug | General Discussion | 6 | August 20th, 2004 05:41 PM |
Query to join records form 2 databases | bdehning | General Discussion | 5 | August 9th, 2004 03:09 PM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Filter by Form query will not populate fields | Brigitte P | Running & Setting Up Queries | 2 | May 27th, 2004 12:59 PM |