Results 1 to 5 of 5
  1. #1
    sburch032870 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    3

    Need a query that can issue a numerical number for each record that shows up in the query.

    I issue multiple RFIs (requests for information) for multiple projects. The trouble that I have is that I am having to make a table for each project, which means that there is a lot of work for each new project. What I would like to do is setup a table that allows me to enter the RFIs and and then run a query based on the project number and have the query issue a sequential number as the RFI no. This will allow me to change all of the forms and reports based on the data. Any suggestions would be really appreciated. I use the RFI and project number together. PROJECT.RFI.Seq# HFF.RFI.001

    RFI No. RFIProject RFIDate RFICompany RFIPOC RFISubject RFIIssueLocation
    001 HFF
    9/11/2015 ABC, Inc. Person Thermostat: Proposed Location
    Back-of-House Wall South of Reach In Freezer
    1






    RFI No. RFIProject RFIDate RFICompany RFIPOC RFISubject RFIIssueLocation
    001 LESCHI 11/13/2015 XYZ, Inc Person 2 Floor/Ceiling Assembly Between Retail and Living Space Floor/Ceiling Assembly Between Retail and Living Space
    1








    Sample RFI Form

    Click image for larger version. 

Name:	11-13-2015 4-07-45 PM.png 
Views:	17 
Size:	7.5 KB 
ID:	22710

    Thanks for looking.......
    Last edited by sburch032870; 11-13-2015 at 06:11 PM. Reason: Add sample form

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend you start at the 30,000 ft level and tell us what all this means in business terms.
    Along the lines of this made up scenario:

    We are a consulting/project management company. We have projects that involve... in several locations....
    Each project is identified by a.... and is described...... Projects are broken down by task. We do estimates and actual resource utilization..... From time to time we have requests for information, such as, ..... We record all changes and requests for information such that we can report same by Project, by Date, by Requestor, by Location.....blah, blah

    Once readers understand your requirement in some business context, I'm sure some options will follow.

    Good luck
    Last edited by orange; 11-14-2015 at 05:54 AM.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And hopefully you're redesigning so you don't have a table for each project? A normalized design would be a single "projects" table, with a field to identify the project. Then you simply query that table/related tables for the project you want. You can increment a project number using DMax() + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    To help with your design as Paul indicated, you may wish to read this:

    http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf

  5. #5
    sburch032870 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    3
    Thank you. I actually have a project table that Project field is an fk. The trouble is mainly that I run into folks with specialized project numbers to I cannot increment them. In fact this client has three companies and they all use a different project numbering system (I know, drives me nuts.) So what I am trying to do is be able to enter this data in this form into the various related tables and then when I qry on qry the project related records and have that qry number the records so that I can blend them into a sequential RFI no field on the form and reports. It really is better management using separate tables for each project but it just gets to be a pain having to jump from form to form. I am sure there are some other ways that I have not thought of but I am not there yet in my development abilities. I am a builder that is trying to teach himself this stuff as I go and most of my educational time is spent keeping up with building code changes.

    Thank you again...

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

Similar Threads

  1. Replies: 2
    Last Post: 03-02-2015, 03:06 PM
  2. Replies: 3
    Last Post: 04-13-2012, 10:39 AM
  3. Replies: 14
    Last Post: 02-23-2012, 06:32 PM
  4. Replies: 5
    Last Post: 10-15-2010, 01:19 PM
  5. Making New Record Number Next Numerical Number
    By jhillbrown in forum Access
    Replies: 1
    Last Post: 03-10-2010, 11:06 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