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 |
#11
|
|||
|
|||
Table design question re duplicates fields
On Thu, 7 May 2009 22:22:02 -0700, lmcc007
wrote: Where did I go wrong? I am glad you understand what I am trying to do, but maybe I missed a step or something. What do you think? Probably in Query1. Please open the query in design view; select View... SQL; and copy and paste the text to a message here. My guess is that you have the default join type - "inner join". If so you can fix it yourself - doubleclick on the join line in the query design window, and check the radio button that says "Show all records from tblPhoneTypes and matching records from tblPhones". -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Table design question re duplicates fields
Below is what I copied and pasted in SQL:
SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType, tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType ORDER BY tblPhoneTypes.Seq; Is there a way to upload your database on this site? Thanks! "John W. Vinson" wrote: On Thu, 7 May 2009 22:22:02 -0700, lmcc007 wrote: Where did I go wrong? I am glad you understand what I am trying to do, but maybe I missed a step or something. What do you think? Probably in Query1. Please open the query in design view; select View... SQL; and copy and paste the text to a message here. My guess is that you have the default join type - "inner join". If so you can fix it yourself - doubleclick on the join line in the query design window, and check the radio button that says "Show all records from tblPhoneTypes and matching records from tblPhones". -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Table design question re duplicates fields
Doubleclick on the join line in the query design window, and
check the radio button that says "Show all records from tblPhoneTypes and matching records from tblPhones". I checked it and it is set up correct. How is this telling the form to always display Business, Business 2, Business Fax, and Mobile even if there is no phone number entered? "John W. Vinson" wrote: On Thu, 7 May 2009 22:22:02 -0700, lmcc007 wrote: Where did I go wrong? I am glad you understand what I am trying to do, but maybe I missed a step or something. What do you think? Probably in Query1. Please open the query in design view; select View... SQL; and copy and paste the text to a message here. My guess is that you have the default join type - "inner join". If so you can fix it yourself - doubleclick on the join line in the query design window, and check the radio button that says "Show all records from tblPhoneTypes and matching records from tblPhones". -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Table design question re duplicates fields
On Fri, 8 May 2009 10:29:01 -0700, lmcc007
wrote: Doubleclick on the join line in the query design window, and check the radio button that says "Show all records from tblPhoneTypes and matching records from tblPhones". I checked it and it is set up correct. How is this telling the form to always display Business, Business 2, Business Fax, and Mobile even if there is no phone number entered? Sorry... I think I gave you some bad advice! Let me think about this and I'll post back. The Master/Child Link is (of course) leaving out the records with no CompanyID. I think there's a way to do it but I'm a bit frazzled today! -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Table design question re duplicates fields
In case I wasn't clear here is what I am trying to do and what I have done so
far. I am trying to create a form that looks like the Outlook Contact Manager’s form. For example: Phone Numbers--------------------------------- Business 800-555-1212 Business 2 Business Fax 800-555-1213 Mobile I want the form to always display Business, Business 2, Business Fax, and Mobile whether there is a phone number in the field or not. And, if I need to add additional phone numbers I can. Below is what I have created: tblCompanies CompanyID AutoNumber (PK) CompanyName Text (Indexed, No Duplicates) ------ tblPhoneNumbers PhoneNumberID AutoNumber (PK) PhoneType Text Indexed = Yes (Duplicates OK) Lookup: Display Control = Combo Box Row Source Type = Table/Query Row Source = SELECT [tblPhoneTypes].[PhoneType], [tblPhoneTypes].[Seq] FROM tblPhoneTypes ORDER BY [Seq]; Bound Column 1 Column Count 2 Phone Text (20) Input Mask = !999\-000\-0000;0;_ Allow Zero Length = Yes Indexed = Yes (No Duplicates) ------ tblPhoneTypes PhoneType Text (PK) Seq Number (Long Integer) ------ Query1 SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType, tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType ORDER BY tblPhoneTypes.Seq; ------ frmCompanies Text Box = CompanyName Control Source = CompanyName Query1 subform Source Object Query1 subform Link MasterFieds CompanyID Link Child Fields Company ID ------ Relationships tblPhoneTypestblPhoneNumbers tblPhoneTypes tblPhoneNumbers PhoneType PhoneType Attributes: Not Enforced RelationshipType: One-To-Many tblCompaniestblPhoneNumbers tblCompanies tblPhoneNumbers CompanyID 1 Many CompanyID Attributes: Enforced, Cascade Deletes RelationshipType: One-To-Many |
#16
|
|||
|
|||
Table design question re duplicates fields
John,
To me this looks waaaaay simpler than the discussion. I think their subsequent post reinforced my guess in my first post......I think that they are expecting to see display of non-existent records. E.G. when when no "Business Fax" or "Mobile" etc. record has been entered for a company, a "Business Fax" and "Mobile" record sill show up, with a blank for the phone number. |
#17
|
|||
|
|||
Table design question re duplicates fields
Yes Fred, that is what I am trying to do.
I looked at MS Outlook Contact manager form layout. It appears to be done: 1) Business, Business 2... is a label or button, which if double click it will open a form to enter or revise the phone number attached to the label. 2) The down arrow button will let you choose the label--meaning, Assistant, Car phone, Business, Mobile... After you choose the label it will display a blank box to enter a phone number if you have not already entered a phone number for that label. 3) All the phone numbers has to be in one table because you can filter, sort, and so by the phone number. It is probably very simple but I can't figure it out. I am a dummy with Access. I am trying to learn, once and for all, the right way to set up my tables. So, what do you suggest I do? Thanks, for your help! "Fred" wrote: John, To me this looks waaaaay simpler than the discussion. I think their subsequent post reinforced my guess in my first post......I think that they are expecting to see display of non-existent records. E.G. when when no "Business Fax" or "Mobile" etc. record has been entered for a company, a "Business Fax" and "Mobile" record sill show up, with a blank for the phone number. |
#18
|
|||
|
|||
Table design question re duplicates fields
Hello IMC007,
I think that you might have said a couple conflicting ways that you want this: 1. All of the labels there all of the time 2. Like Outlook (don't know what this is because I don't use Outlook) 3. A "Dropdown" to select the number type, and then a blank to fill it in, per your last post. I'd suggest #3. Make a tiny table listing your phone number types. PhoneNumberType_LU (shorten that) Make a dropdown box on the phone number type field in yur sub form. Use your little PhoneNumberType_LU table as your source for this. Now you're done. |
#19
|
|||
|
|||
Table design question re duplicates fields
I was just explaining how MS Outlook Contact manager works. MS Outlook is a
program that comes with MS Office Professional, which can be used to calendar dates, to-do list, send and receive e-mails, and add business cards to the contact manager. And, I tried #3 earlier this week but it didn't work because each record may have more than one phone number and so on. "Fred" wrote: Hello IMC007, I think that you might have said a couple conflicting ways that you want this: 1. All of the labels there all of the time 2. Like Outlook (don't know what this is because I don't use Outlook) 3. A "Dropdown" to select the number type, and then a blank to fill it in, per your last post. I'd suggest #3. Make a tiny table listing your phone number types. PhoneNumberType_LU (shorten that) Make a dropdown box on the phone number type field in yur sub form. Use your little PhoneNumberType_LU table as your source for this. Now you're done. |
#20
|
|||
|
|||
Table design question re duplicates fields
I know what Outlook is, I just stopped using it (and switched to free
Thunderbird) a long time ago. It has the one big problem where it sends attachments out as unreadable-by-many .dat files unless you use "plain text" compose mode. I just meant that since I don't use it I don't know it's screens etc. - - - - However, I'm assuming that, for a contact, Outlook has a handful of standardized types of phone numbers, and thus a pre-defined heading for each of them. If that is what you want and can live with, then you'd skip this entire thread, and go back to a single table with a field for each phone number type. Under the structure that the respondents in this thread came up with, you have no such predetermined structure / restructions. If one person has 15 phone numbers, many of non-standard types, their record would have 15. If someone else had only one, their list would have only one. One other possibility would be to automate it so that when you enter a new person, it automatically loads a set of records in the phone number table for the common types. So you'd have titles and blanks for the common phone number types, plus the ability to add others. This would be a lot of work and complexity to go though, resulting in what many would call bad data. In my opinion, make the leap, and go with your #3, which is how it's normally done when databasing things such as yours. It does not have the restriction/problem that you describe....it lets you put an unlimited number of phone numbers under each person, indluding repition of the types, if you so choose. |
Thread Tools | |
Display Modes | |
|
|