Results 1 to 8 of 8
  1. #1
    hodgson94 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    London
    Posts
    3

    Database ID Structure - CRM Database

    Hi There,

    I'm creating a fairly straightforward database for a company that works mainly on exhibitions. They collect the customer's data and need to produce a sales receipt with an agreement. I'm looking for some advice on setting up the customer ID, order ID.



    I'd like the customer ID to be constructed as follows, 6 digit date figure of when the record is created, then a two digit counter, then the event ID where the record is being created. E.g 260818/01/12. Date/Counter/EventID

    I'd then like the sale order ID to be fashioned from this, ig Date/Counter/EventID/OrderID. This way over time staff would be able to know where to look for certain files or reference an Event ID sheet and instantly know who to speak to in the office etc.

    I know creating calculated fields in ID fields can start getting messy so just looking for some advice on the best way of doing this.

    Thanks Jake

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Use Access autonum fields. You want need to do ANY programming since they are self creating.
    Your Orders would then use these IDs.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Use the Keep it Simple principle ---1 fact 1 field. You can assemble/concatenate atomic fields into display patterns that make sense to users.
    Using autonumbers for IDs is a proven approach.

  4. #4
    hodgson94 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    London
    Posts
    3
    Ok, Brilliant thanks guys.

    One thing I forgot to mention was the fact I need to have multiple years for each Event and I'm sure if I'm thinking right with the way I currently have my relationships set. So I have a list of events that we attend every year and then a separate table that contains the dates of the event of that year. I've attached a screenshot of my current setup but I not sure where I'm going wrong, please advise if I'm on the right track.

    Click image for larger version. 

Name:	DB Relationships.jpg 
Views:	43 
Size:	90.7 KB 
ID:	35261

    Any help is massively appreciated! Let me know if I'm not quite making sense.

    Thanks Jake

  5. #5
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    Whenever possible, try to make sure that you name the ID columns so that you can tell the difference between the different tables. That will come in handy when you are joining tables. For example, instead of having a bunch of your identification columns having the same name, try something like StatusID or StatusSysID on table tblStatus. It also looks like you have a bunch of unrelated tables.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    hodgson94,

    Nobody can comment on your relationships nor tables until you provide a clear description of the business.
    And there is an expression, probably related to Richard Feynman, if you can't describe it in simple terms, then you probably don't really understand it. Your "business rules" lead to your database relationships.

    Tell us about Events, Customers, Orders etc in a day in the business sort of scenario. Status, Quantity, QuoteStatus, SalesPerson, Products don't seem to be referenced yet in this thread.

    Remember we know nothing about you, your environment or your business.
    Good luck.

  7. #7
    hodgson94 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    London
    Posts
    3
    Hi Orange,

    Fair point, understood. It was rather hasty, apologies.

    Basically we sell a product on events throughout the year, hence the event and event year table. We attend roughly the same 60 events every year but thought this’d be the easiest way to implement this data as it is fairly important for reporting and linking records.

    The primary flow would be to launch a form where you enter the customer data ie contact info, addresses etc, then you would click “Save Customer & Create Sales Receipt’ then saving the current customer record and launching the orders form that then sets the order’s customer ID. In the order form the user would also select the event & year.

    I hope this makes more sense, please left me know if you would like anymore information.

    Thanks

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    One way to make this:
    You have forms to register events (with a subform to register event year data), products, and customers - based on tables tblEvents, tblEventYears, tblProducts and tblCustomers. This part of database I usually define as registries - the information which provides the database with info to work with on.
    Then you need tables tOrders and tOrderDetails. Table tOrders has Foreign Key fields for event year, and for customer. Table tOrderDetails has a row for every product sold through given order, and a Foreign Key for order number/order ID.
    The main form will be the unbound form where you can select the event form tblEventYears and customer in unbound combo boxes (you can have an unbound combo box to select the year and set the filter condition for event year combobox too). The unbound form has a subform sfOrders with single form fOrders as source, based on table tblOrders.
    The form fOrders has a subform sfOrderDetails with continuous form fOrderdetails as source, based on table tblOrderDetails.

    Adding a new record into sfOrders creates a new order automatically bound to event of certain year and to customer from Main form.
    After an order is registered, adding rows to sfOrderDetails allows to register products sold to customer at this year event through active order. Order detail rows are automatically bound to order active in sfOrders.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Structure
    By radick_201 in forum Access
    Replies: 1
    Last Post: 01-13-2016, 10:24 AM
  2. Database Structure help
    By crazyrat25 in forum Access
    Replies: 3
    Last Post: 11-20-2014, 05:28 AM
  3. Little help in my database structure
    By F0u4d in forum Access
    Replies: 1
    Last Post: 04-10-2014, 06:42 AM
  4. Replies: 1
    Last Post: 11-03-2010, 10:41 AM
  5. Help with Database Structure
    By scottay in forum Access
    Replies: 8
    Last Post: 06-30-2010, 08:16 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