Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    These tables:-
    tblEnterprises contain your list of Enterprises.
    tblEntities
    contain your list of Entities.
    tblNrYears contains a list of the year values you use to calculate Authorised for Periods.
    tblPeople
    contain your list of People.



    You just need to replace the current data wit your actual names required.

    The others tables (tblPeopleEnterprises; tblPeopleEntities; tblPeopleVehicles) will be filled through the form - Yes

    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  2. #17
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by mackson View Post
    All these enterprises/entities employe work on the same site under the same rules. So transferring an employee to one Enterprise/entity to another doesn't change much things to his clearance validity unless if he needs new one. What are do you think of the two relationship I posted previously (JPEG files) ? Are they correct?
    When doing so, then it doesn't mean that employee belongs to either one or another entity. For DB, it does mean that the employee did always belong to both of those entities - i.e. to 2 different entities. And whenever you generate some reports later, you probably get this employee accounted twice!"

  3. #18
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by ArviLaanemets View Post
    When doing so, then it doesn't mean that employee belongs to either one or another entity. For DB, it does mean that the employee did always belong to both of those entities - i.e. to 2 different entities. And whenever you generate some reports later, you probably get this employee accounted twice!"
    Hi!
    I deleted the relationship to insert my data in the different tables, then rebuild the relationship between the different tables.
    Unfortunately I ma having commas appearing with the name&surnames below in the combo box of the frmpeople where you select the surname as you can see below. is it due to my data ? I inserted them exactly the way they were in the previous database.

    Click image for larger version. 

Name:	FrmPeople.png 
Views:	14 
Size:	6.4 KB 
ID:	49925

    Regards

  4. #19
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Why did you rebuild the Relationships??

    Can you show what you currently have?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #20
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    I thought that I needed to delete the relationship before importing my new data from excel files and rebuild the tables
    here is a screenshot of the relationship. I updated the tables with green tick
    Click image for larger version. 

Name:	ClearancedbRelationship.png 
Views:	12 
Size:	40.4 KB 
ID:	49929
    Attached Thumbnails Attached Thumbnails ClearancedbRelationship.png  

  6. #21
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Ok That looks OK

    All you need to do is delete the Combobox in the Header for Select Surname.

    Then create a new Combobox for Searching for a field on your Form
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #22
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    I tried it. But I still have empty field in the combox box before reaching for the name and surname while rolling it. I have at least a couple of hundred lines empty. Previously it was a hundred commas.

  8. #23
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    OK Upload your current version of the Database so we can take a look
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by mackson View Post
    Unfortunately I ma having commas appearing with the name&surnames below in the combo box of the frmpeople where you select the surname as you can see below. is it due to my data ? I inserted them exactly the way they were in the previous database.
    This is creating a record selection combo on form? As I can't get any additional info from picture, I'll give a full description how I'd do it.

    1. Creating an unbound combo (the ControlSource property is empty);
    2. Setting the RowSource property for this combo. In your case I'd advice something like
    SELECT PeopleID, Surname & ", " & Firstname & "(" & CStr(PeopleID) & ")" As FullName FROM tblPeople ORDER BY 2 ASC
    (Using Surname only is not good idea, as there always is a possibility there will be more than 1 people with same surname. There is a lesser, but no zero, probability there will be some people with same Surname and Firstname too. When you want to cover those cases too, then you can use the part of Fullname showing PeopleID value as an example how to add some additional info.);
    3. Set the BoundColumn property to 1. (In your case, it means the value of combo will be PeopleID of selected person);
    4. Set the ColumnCount property to (number of columns)/(number of leftmost columns you want to include) in RowSource query. (In your case, it will be 2);
    5. Set the ColumnWidths property so, that the width of the one column of RowSource column you want the combo to display will be >0, and the width of all other columns will be 0. (In your case, something like "0;2.5" - I'm not sure about list delimiter you use);
    6. Create the AfterUpdate event for combo, which calls the procedure to locate and activate the record with selected Bound Column value in certain field of form source.

    As in your case, the combo is created, you simply have to check all properties there. When those are OK, and the combo is not working anyway, the the problem must be in tblPeople probably.

  10. #25
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Hi !
    You are right, it’s a problem with the tblPeople. As you can see below there are empty fields in that table. This happens when I import the data from an excel file and let Acces insert a primary key. After deleting the table and re-importing the data, I defined manually my primary key in the tblPeople before building the relationship. Now it is fine.

    Click image for larger version. 

Name:	EmpyRecordTblPeople.png 
Views:	10 
Size:	40.3 KB 
ID:	49936


    I can now start filling manually the database with the different information. It may take a time but I may follow up, if you don’t mind with questions regarding the queries, but that should be in another forum, isn’t it ? Once again thank you all. Special thanks to mike60smart

    Regards

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 26
    Last Post: 11-03-2021, 09:11 AM
  2. Replies: 1
    Last Post: 08-15-2020, 04:34 AM
  3. Design vs Article (=Design+Materials) problem
    By emihir0 in forum Database Design
    Replies: 14
    Last Post: 04-20-2015, 03:03 PM
  4. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  5. what are some benefits of a DBMS like MS Access?
    By helpporfavorplz in forum Access
    Replies: 3
    Last Post: 02-23-2010, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums