Results 1 to 4 of 4
  1. #1
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    Sequential Numbering using a query

    Hello all!

    I have a client who builds displays for windows and doors, etc. I have set up their order entry to choose the fixture (display) and then choose the products (windows/doors) to go into it. They are moving from a dreadful Xcel spreadsheet and are used to seeing things a certain way . So in the order form, the fixture has to be bold and the products under it, not in bold. This works great, until they realize that they forgot to add a line item product. Of course, you can't just insert it where it should be, so I came up with a sequential numbering routine, the basic:
    Code:
    Forms![Orders]![BuildItfloor].Form![OrderDetailsfloor]!LineNum= Nz(DMax("LineNum", "OrderDetailsFloor", Storenum=[storenum]), 0) + 1
    , so when they add an out of order piece they can change the LineNum to something that puts it in with the other related items. Clear as mud? This works great in one section (first image), but there are other displays that it's necessary to autofill pieces that go with them, using a query. Again, fixture needs to be bold and products not. Sequential numbering does not work in this instance (second image). Any tips on how I can get it to work? Images attached. I thank you in advance for any help you can send my way. Click image for larger version. 

Name:	floororderexample.PNG 
Views:	15 
Size:	47.9 KB 
ID:	38783Click image for larger version. 

Name:	orderexample.PNG 
Views:	13 
Size:	61.7 KB 
ID:	38784

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Gina,

    I had a similar issue when developing a database for manufacturing. I resolved it by creating a "Relationship Table". This table has a primary key, a ParentPN Field, and a ChildPN Field ans also an Order Field, as a minimum. The ParentPN Field is of course the Part Number of the Parent item and the ChildPN Field is for the Various Part Numbers that go into making the Parent Item. See Below.

    Click image for larger version. 

Name:	relationship example.PNG 
Views:	15 
Size:	4.6 KB 
ID:	38786

    As you can see the last entry, ChildPN X1235 is used twice in this example, it is used as the 2 item and also as the last item.

    You need only to create 1 table and can have as many items in it as you need, mine has about a hundred top level part numbers and over 1500 total rows of data.

    I hope this helps, if you need any further info regarding this approach, let me know.

    Thanks

    Dave

  3. #3
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Thanks Dave! I'm confused though (doesn't take much :-)) I have a "Fixture" table and a Product List of products that go into those fixtures. The order numbering actually needs to happen in the Order Details table as items are chosen for the fixture. Many pieces auto fill as required. I'm not understanding, in your example, how I would accomplish this.

    Thanks again!!

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Gina,

    I use a subform, See Pic

    Click image for larger version. 

Name:	DHR Pic.PNG 
Views:	6 
Size:	40.6 KB 
ID:	38803

    The subform is sorted by the Order #. You can also change the Order # in the subform and resort it.

    Dave

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

Similar Threads

  1. Sequential Numbering based on Criteria
    By BNeff in forum Access
    Replies: 3
    Last Post: 05-17-2019, 02:55 PM
  2. sequential numbering with criteria
    By Harmoesh in forum Queries
    Replies: 1
    Last Post: 07-04-2018, 05:16 PM
  3. Add sequential numbering to groups of data
    By astephan in forum Access
    Replies: 8
    Last Post: 06-07-2018, 08:34 AM
  4. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 08:20 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM

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