 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2005Autonumber using alphanumerics
Hi there, I received some good advice relating to having an alphanumeric reference number, which I have been able to create and it works well in a query. Thanks to those who took the time to give me the advice. However, the data that I am able to obtain from the query is not displayed in the underlying table or form. The Table has 3 fields relative to the query; "ClientID Number", a 3 digit autonumber, "ClientID Name", a 3 digit manually created alpha figure, and "ClientRef", which displays a combination of both these data to display the alphanumeric figure that I need. The query draws on the information from the "Corporate Client Details" Table, where all related fields are found. In the underlying table I tried to use the lookup wizard to recall the data from the query for display in the table/form, but when doing so got the message "no valid fields can be found in 'Corporate Client Details Query'. You may have selected a query that uses the table your adding the lookup column to. Please select a new source". I guess the error message is self-explanatory, but I'm wondering how I can get around this problem. Off the top of my head it seemed appropriate to then create another table which holds the alpha and the numeric fields, along with the client name, then only keep the ClientRef, which is a combination of the two fields in the "Corporate Client Details" Table. Upon doing so, I was still unable to recall the data from the query using the lookup wizard. Is there a way around this? The other problem that I have is that I want the alpha figures in the ClientRef to appear in uppercase. I have enabled this to happen in the table, but it does not in the query when I combine the two fields together. Does this matter that it doesn't appear in the query, or is it that once I manage to combine the "ClientID Number" and "ClientID Name" in a single field I can then set it to view in uppercase? Thanks in advance Katharine I'm not sure I understand, but I suspect you are working directly in the table, rather than via queries and forms. Access (JET) tables store data, forms (and reports) display data. A search through this newsgroup (tablesdbdesign) will reveal a strong consensus against using lookup data type fields in your table definitions. The more commonly recommended approach is to create independent tables holding your lookup values, and to use queries to return those values to combo boxes in forms. Good luck Jeff Boyce <Access MVP> > Hi there, > I received some good advice relating to having an alphanumeric reference [quoted text clipped - 32 lines] > Thanks in advance > Katharine Hi Jeff, Thanks for the prompt response. I've tried what you have recommended, and it's not quite how I want the form to function. What I want the form to do is to recall the data from the "ClientID Number" and "ClientID Name" fields and display them as one field on the form relative to the client data being recalled, as opposed to having a combo box which lists the various options available to me. So as I scroll though the client listing it will display the "ClientRef" relative to the client I am looking at at any particular time. I hope this is a little clearer for you this time. If you have any other suggestions I would be most grateful. Thanks in advance Katharine > I'm not sure I understand, but I suspect you are working directly in the > table, rather than via queries and forms. Access (JET) tables store data, [quoted text clipped - 58 lines] > > Thanks in advance > > Katharine If the fields are being combined successfully in the query, base the form on the query and add to the form a text box bound to the query field. If ClientID Number is unique to the client then it is all you need for a primary key field; however, you can use a combined-field PK if necessary. If you scroll through the client records (on the form, not the table) you will see the combined fields from the query if you followed the suggestion in the first sentence. If you are working directly with records in a table, stop doing that. Forms are how you interact with the data. If you have successfully combined fields in a query it is possible to view the query in datasheet view, where you can see the combined number directly next to the Client name, but again, this is not the best way to interact with data. You may be trying to treat a table or query as a spreadsheet. They may look similar, but they work very, very differently. Combining fields may be thought of as a sort of calculation. Calculations are not stored except in rare and specific instances (which do not apply in your case), but are instead figured "on the fly." > Hi Jeff, > [quoted text clipped - 95 lines] >> > Thanks in advance >> > Katharine Hi Bruce Thank you so much for your response. I have done as suggested and it works perfectly. Ever grateful Katharine > If the fields are being combined successfully in the query, base the form on > the query and add to the form a text box bound to the query field. If [quoted text clipped - 113 lines] > >> > Thanks in advance > >> > Katharine I wonder if you can help with another problem thats reared it head. in order to test how the database is working I only have 2 records entered. The on the form and query it has created a duplicate of each record with the wrong ClientRef for each record, so for example 301PET is peterInc, 302JON is jonInc, which is there, but it shows another record as 301PIN as jonInc, and 302JON as peterInc. Any idea what has happened here? In the intial table that I based my form on originally this didn't happen. Thanks in advance Katharine > Hi Bruce > [quoted text clipped - 121 lines] > > >> > Thanks in advance > > >> > Katharine Are there additional records in your table, or just the original two? I'm not exactly sure what is going on, but I suspect there is a problem with your calculation that combines the two fields. Is your query based on the table? If so, in query design view click in the top of an empty column and type: CombinedID: [ClientID Number] & [ClientID Name] Be sure to use the correct field names, including spaces, underscores, etc. Switch to datasheet view. If it looks OK, use the query as the form's record source. Click View > Field List, and drag CombinedID onto the form. This should be all that's needed. By the way, you referred to ClientID Number as a three-digit autonumber. Are you sure about that? Is it listed as autonumber in table design view? If it is a number you have created, is it listed at Indexed (bottom left in table design view) as Yes (no duplicates)? If not, it needs to be. Without knowing more about your database it is impossible to say. You will need to post your table structure and relationships, and the SQL from your query. To post the table structure, so something like this: tblClient ClientID_Number (primary key) ClientID_Name Phone, etc. Do the same for other tables that are involved in the problem. To get the SQL for the query (the script that is going on behind the scenes), click View > SQL View. Copy what you see and paste it into your reply. >I wonder if you can help with another problem thats reared it head. in >order [quoted text clipped - 180 lines] >> > >> > Thanks in advance >> > >> > Katharine Hi Bruce I only have 2 records in my table, no others. My calculation combining the two fields looks to be correct; it appears like this: Client Ref: [ClientID Number] & [ClientID Name] and yes, the query is based on the table, and I clicked on a blank field and entered the calculation you suggested and the problem remains when I switch to datasheet view. I still have a duplicate copy of each record with the wrong Client Ref. The ClientID Number is an autonumber and listed as such in design view, its field size is long integer, new values set to increment, and indexed as Yes (No Duplicates). The 2 tables that I have used to create the query are Corporate Client Details and Corporate Reference, the latter holding the ClientID Number and ClientID Name fields that the query is based on. Presently I have these 2 tables, although the database will draw on information from 2 other tables, Active Payments and Closed Payments. I have not included these tables as they are yet to be completed and no relationships have been made to the first 2 tables at this stage. tblCorporate Reference ClientID Number (Primary key, and Automunber) ClientID Name Corporate Name Division tblCorporate Client Details Client Ref (the combined ClientID Number and ClientID Name does not appear here in datasheet view, hence the reason the form is based on the query and no relationship established) Organisation Name Branch Title1 Surname1 First Name1 Telephone1 Fax1 Mobile1 Email1 Title2 Surname2 First Name2 Telephone2 Fax2 Mobile2 Email2 Address Town County Post Code Web Address Notes Below is the SQL for the query. SELECT [ClientID Number] & [ClientID Name] AS [Client Ref], [Corporate Client Details].[Organisation Name], [Corporate Client Details].Branch, [Corporate Client Details].Title1, [Corporate Client Details].Surname1, [Corporate Client Details].[First Name1], [Corporate Client Details].Telephone1, [Corporate Client Details].Fax1, [Corporate Client Details].Mobile1, [Corporate Client Details].Email1, [Corporate Client Details].Title2, [Corporate Client Details].Surname2, [Corporate Client Details].[First Name2], [Corporate Client Details].Telephone2, [Corporate Client Details].Fax2, [Corporate Client Details].Mobile2, [Corporate Client Details].Email2, [Corporate Client Details].Address, [Corporate Client Details].Town, [Corporate Client Details].County, [Corporate Client Details].[Post Code], [Corporate Client Details].[Web Address], [Corporate Client Details].Notes FROM [Corporate Client Details], [Corporate Reference]; Maybe you can see what I have failed to and advise me as to what the underlying problem is. Thanks in advance Katharine > Are there additional records in your table, or just the original two? I'm > not exactly sure what is going on, but I suspect there is a problem with [quoted text clipped - 27 lines] > SQL for the query (the script that is going on behind the scenes), click > View > SQL View. Copy what you see and paste it into your reply. See responses inline. > Hi Bruce > > I only have 2 records in my table, no others. My calculation combining the > two fields looks to be correct; it appears like this: > > Client Ref: [ClientID Number] & [ClientID Name] Are you attempting to perform this calculation in tblCorporate Client Details? If so, delete the field. Combining two fields is a type of calculation. Calculations are stored in tables only under the rarest circumstances, but as far as I know it is not even possible to perform a calculation in a table. > and yes, the query is based on the table, and I clicked on a blank field > and [quoted text clipped - 25 lines] > Corporate Name > Division Can a corporate client have more than one division? If so, Division would be a table by itself. It would contain its own primary key (PK), a ClientID Number as a foreign key (FK) field, and any of the details that are now contained in tblCorporate Client Details. No big changes are needed. Rename tblCorporate Client Details tblDivision. Delete ClientRef. Add a PK field (autonumber is fine) and ClientID Number. It table design view, be sure that the data type for ClientID Number is Number. Click Tools > Relationships. Add the two tables (tblCorporate Reference and tblDivision). Drag ClientID Number form one table, and drop it on ClientID Number in the other table. Click Enforce Referential Integrity. Close the Relationships window. Now make a query based on tblCorporate Reference. Add the calculated field I suggested to combine the two fields. Now build a form based on that query, and another form based on tblDivision. In form design view, drag the icon for tblDivision onto tblCorporateReference. Similarly, if each Division could have several branches, build a Branch table linked to tblDivision just as tblDivision is linked to tblCorporate Reference. The forms would work similarly as in the example above. The approach is the same whether or not the company has multiple Divisions or Branches. If a company has one Division you can call it Home Office or something, and allow for the possibility they will add a Division in the future. Same with Branches. > tblCorporate Client Details > Client Ref (the combined ClientID Number and ClientID Name does not [quoted text clipped - 40 lines] > Client Details].Notes > FROM [Corporate Client Details], [Corporate Reference]; It seems that Client Ref is a table field, as mentioned earlier. Combine the fields in the query, and do not store them in a table. I'm guessing a bit about what you are trying to accomplish, but the general principles behind table relationships apply no matter the situation. A good rule for table design is that you should be able to describe a table's purpose in one sentence without using the word "and". Having said that, don't get too literal. Personal information can include address and phone number, but not personal information and employment history, for instance. In your case, Company and Divisions are related, but do not belong together (if my assumption is correct about a company having several branches). > Maybe you can see what I have failed to and advise me as to what the > underlying problem is. [quoted text clipped - 43 lines] >> SQL for the query (the script that is going on behind the scenes), click >> View > SQL View. Copy what you see and paste it into your reply. Hi Bruce The calculation that I did was in the query. Sorry I didn't make this clear to you. You also asked if the corporate client can have more than one division, the answer is yes. However, not all corporate clients will have divisions, therefore I have created a test database with three tables holding the corporate client information; tblCorporate Client Name, which holds the ClientID Number and ClientID Name; tblDivision, holding the DivisionRef (if applicable) relative to the corporate client; and tblCorporate Details, holding the clients contact details. I have formed a relationship with the field ClientID Ref in both the tblCorporate Clent Name and tblDivision in the test database which works well in datasheet view, however, I have created a query using the three tables which I want to use to test the functionality of a form that would follow, and the query will not work. The error message that comes up is "The specified field '[ClientID Ref]' could refer to more than one table listed in the FROM clause of your SQL statement". Although I understand why the error message appears, the 'ClientID Ref' field is what the relationship between the two tables is based on, therefore I am uncertain as to how to overcome this problem and pursue further. Is there a way I can reword the calculation to get the query to run, or is it a little less straightforward than that? Thanks in advance Katharine > See responses inline. > [quoted text clipped - 171 lines] > >> SQL for the query (the script that is going on behind the scenes), click > >> View > SQL View. Copy what you see and paste it into your reply. Where did DivisionRef come from? In what relationships does it participate? Is it a primary key? From what I can understand, ClientID Ref is a field in two tables. That is probably why the query does not work. What is the purpose of tblDivision? I understand that a company may or may not have divisions, but what is stored in that table? I assume that each division would have its own address and contact information. How do you relate an address to a division? Just guessing a bit, but try this: Keep your tblCorporate Client Name and your tblCorporate Details, except add ClientID Number as a foreign key field in tblCorporate Details, and an optional field for describing if the details are for a divsion of the company. Establish a relationship between ClientID Number in the two tables containing the field. Click Enforce Referential Ingegrity. Build a form and subform as previously described. > Hi Bruce > [quoted text clipped - 235 lines] >> >> click >> >> View > SQL View. Copy what you see and paste it into your reply. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.