Custom Autonumber Based On Lookup

Jul 8, 2013

Custom Autonumber based on lookup. I am creating an access database where autonumbers to be work.

I have a table with Segment Name my main table will lookup the values of segment table.

Based on the Segment I choose Autonumber has to be created

My main table to house each record (tbl_import) has the following fields:

ID
Segment
Port
MOT
TOTAL CIF

Child table - Segment contains
Development
Deployment

Testing

So based on segment field which I choose

Development autonumber has to set DEV-1001 and Deployment has to set DEP-2001, TEST-3001

My thought is if there a way to code the Segment field to lookup the segment what I choose based on the Autonumber series starts (similar to a vlookup in excel), then concatenate the DEV in the Record ID field.

View Replies

Custom Autonumber

Mar 29, 2005

I am in the process of developing a DB for work. The DB is used to track incident reports and lost/found property reports.

I was interested in using 2 custom autonumbers for 2 different reports within the same DB. I would like to use the format of "I" for an incident report, "F" for the found property reports. So when I start a new Incident report it would generate the following number; "I05-03-001" The "I" specifing that it is an incident report, "05" for the current year, "03" for the current month, and "001 as the next sequential number for the reports for that month.

Does anyone have any suggestions on how I can get this done? I would very much appreciate your help.

John

View 1 Replies View Related

How Do I Create A Custom Autonumber

Mar 2, 2005

I currently have a few tables that use an autonumber as the primary key, however, I would like the autonumber to start with a series of letters if possible. For example: instead of it creating an ID of 1, then, 2, 3, 4, and so on, I would like it to append lets say "ABC" to the front of it; ABC1, ABC2, ABC3, etc.

AP

View 2 Replies View Related

Primary Key...Autonumber Or Custom?

Jul 10, 2005

Just a general question...
When you make a primary key is it better to use and Autonumber or a Custom one? I have for the most part used auto...easy, convienient and well I have never had a problem yet (been pretty serious on Access for about 1 year). But I just read that ref integ doesn't work on Autonumbers? which would also mean cascading update & deleting correct?
I have looked at Northwind Access sample and all except "Customer" they use Auto. Well then to make a custom Primary field wouldn't I have to do that at the form level? like "CustomPrimKey = Left([CustFirstName],2 & Left([CustLastName],2) & [PriKeySetAtAutoNum]" or something like that to get what I want. I imagine you would automate this as opposed to letting the operator manually enter them.
And then as in the Northwind sample how do you know which tables need the custom numbering and which ones don't.
As I have learned on my own some of the "basics" elude me until they come back to bite me.

Thanks

View 10 Replies View Related

Bounded And Translated Custom Autonumber

Nov 2, 2005

I was hoping to run this by you guys to see if I'm doing anything horribly wrong. I have done a number of searches related to custom Autonumbers and I think this is okay but any suggestions/confirmations would be greatly appreciated.

I want to create a random Primary Key that will also be used as an item #. I know that in many cases this is frowned upon *but* I do not need the number to hold any significance I just want it to be unique. However I want to represent the item # as a set of 6 Hex digits so the standard Long Integer is too large. 16^6 = 16777216. No 0 index so 16777215.

I create my own bounded number with a macro (=Int(Rand(16777216-1))+1 )and put that as the default value. It is indexed and set to no duplicates. Is this correct?

When I display the field in a form or report I call a module which translates the number into Hex and appends leading zeros. I am currently having a problem where inserting a new record does not display the translated index correctly, but after it has been inserted it is fine.

If you have any comments or suggestions about how this could work even better (or if this wont work at all!) please let me know.

View 2 Replies View Related

Forms :: Custom Autonumber With Year And Month

Aug 12, 2013

I need to create an "autonumber" field in the following format:

FYYMMXX

Where F is a constant, YY is the year, MM is the month, and XX is an incremented number. So for example, the first record in August of 2013 would be "F130801".

I've been checking other threads but haven't been able to follow them to a resolution. I think there is a way to do this within my form...

View 3 Replies View Related

Filtering Lookup Value Based On Other Lookup Values

Jul 27, 2015

I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".

Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.

View 4 Replies View Related

Lookup Field / Autonumber Woes

Jun 21, 2006

So, basically my database has quite a number of lookup fields in tables as there is much repeated data, each one references to a seperate table with an autonumber field (ID) and a text field (Value)

Currently I have column one (ID) as the bound column in such cases, as this is what the guy who started me on access said to do, and it's fine until I try and export any query with such a field to Excel.

I have found, through experimentation, that setting the 2nd column (Value) as the bound column allows me to export the values rather than numbers, but I don't know if this is a sensible thing to do? (ie, I don't know what other unexpected effects this might have)

Any guidance here would be very welcome, thanks,
Bogzla

View 8 Replies View Related

Getting A Custom Table Based On DB Data

Nov 9, 2006

I have a table in the Access DB which is structured this way:

ITEMID
FIELDNAME
FIELDVALUE

The Key is ITEMID,FIELDNAME

I need such a configuration because the items I need to store can have whatever fields the user could like. A simple example of the data contained could be:

ITEMID;FIELDNAME;FIELDVALUE
1;FIELDA;1111
1;FIELDB;2222
2;FIELDA;3333
2;FIELDB;4444

I need to load a datagrid in VB.NET displaying a table like this (according to the above example):

ITEM;FIELDA;FIELDB
1;1111;2222
2;3333;4444

I used to just load the data from Access and then build the grid directly from VB, but the records are becoming too many and this approach is just too slow. I was thinking about creating the table I need directly from a query, and then loading the result in a grid; however, this is something I'm having problems doing. Could anyone help me? Thanks.

Just a couple of notes:

- not every field is necessary present for each ITEM, so I need to handle the possibility of DBNull entries for some fields.

- If it can help, I know which fieldnames I can get before querying the DB

View 7 Replies View Related

General :: Custom Table Based On Users Choice Of Fields

May 15, 2013

I want to create a form where by the user can select the fields he requires and the table is created.

Example: I have a table of subjects like maths, english, french, chemistry etc,

The user can choose what subjects are available in that particular school and therefore a table is created based on the chosen subjects.

Is there any way it can be done even with vba?

View 4 Replies View Related

Forms :: Generate Custom Form Based On Query Results

Dec 11, 2013

I'm trying to track daily production at a manufacturing company running many different processes at different locations each running multiple parallel "lines." The tricky part is that the number of lines running and the shift schedules e.g. 2shifts 10hrs/day 4days/week or 3shifts 8hrs/day 5days/week change frequently for each process.

Right now I have a form for process data that specifies the schedule and number of lines running each day. Then I'm running an append query to a "production" table that generates blank production records associated with each item made in each process for the correct shift/line combinations.

Up to now I've been manually changing the date on the append query each time I run it. Then I have a seperate query for each process that pulls out the production records for each day. My problem is that the preferred interface for production data entry is a spreadsheet with the following layout:

--------DAYS LINE 1 DAYS LINE 2 SWING LINE 1 SWING LINE 2
ITEM 1 100
ITEM 2 2250
ITEM 3

which changes each time the shift/line schedule changes for each process. The only way to achieve this layout I know of is a crosstab query which isn't updateable. Ideally, each day the manager will specify the shift schedules and forms will be automatically generated with the correct structure and sent to the process supervisors. I'm open to different form layouts and even redesigning the database completely.

View 2 Replies View Related

Tables :: AutoNumber Based On Location Strings

May 1, 2013

I have two tables that are related: BUCKETS & LOCATION

BUCKETS has these fields:ID (autonum)
Type (type of bucket)
Creation Date (when it was created)
Operational (if it is operational or not (Yes/No).
Location (Location of the bucket)
Notes (notes)

LOCATIONS has these fields:LocationAutoID (Autonumber)
LocationCity
LocationID
BucketsOnSite (can have multiple entries)

What I'd like to have done is for LOCATIONS table to be aware of how many different cities there are. So for instance, if there is multiple location entries for the cities of Vancouver and Toronto in there, I want the LocationID to first know how many Torontos there are and in LocationID to combine the LocationCity and a count (starting from 1) to that field.

So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.

At first I had the expression of [LocationCity] & "-" & [LocationAutoID] in the Expression field in the Field Properties but I quickly realized that it wouldn't count the amount of cities but just add the autonumber and the city (which kind of works, but not ideally).

View 8 Replies View Related

Lookup Value Based On 2 Fields

Jan 16, 2008

I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.

I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".

Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.

So the TblLevels looks like this:

Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3

So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"

any ideas on how i can do this one?

View 10 Replies View Related

Modules & VBA :: Autonumber Based On A Table Content With A Prefix

Oct 15, 2014

I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)

the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.

However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()

so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.

Code:
Public Function GetPrefix() As String
On Error GoTo GetPrefix_ErrorHandler
GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""")
If Len(GetPrefix) <> 2 Then
msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning"
End If

[Code] ....

View 5 Replies View Related

Queries :: Sort Order In Query Based On ID (Autonumber)

Sep 10, 2013

This is something I occasionally see in Access and has been bugging me for quite a while.

As an example, when I have a table (all text fields except for the ID field which is an Autonumber with a unique index - ie just what Access creates when you import data) and I try to make a new table from a query by indexing the Autonumber field in descending order (ie to reverse the order of the table), it doesn't work properly.

So if I have:

SELECT [mytable].* INTO [mytable sorted] FROM [mytable] ORDER BY [mytable].[ID] DESC;

When I preview the data (ie run the select query to have a look at it), it looks fine.

When I change the query to a 'Make Table' and I then I check the table it makes, the order changes part-way down the list, so looking at the ID field it runs from number 2669 down to 2087 correctly, then it goes from 1960 to 1956, then 1803 to 1799, then 1751 to 1747, etc etc etc. After a while it seems to correct itself again, and orders normally down to #1

I'm using Access 2002.

View 5 Replies View Related

General :: Searching For Records Based On Only Highest Autonumber

Jul 30, 2012

I have a database in which the year is stored in a table called year with as laid out below

"YearID" autonumber field and the
"Year" field which is a date field.

I am trying to search for records via a query for just the current year, i.e. the highest autonumber and I am having no success.

View 4 Replies View Related

Queries :: Grab Custom Fiscal Year Count Based On System Clock Date

Aug 13, 2015

How do you grab a custom Fiscal Year's values based on the system clock's date?

I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.

I need the System Clock's FY value in this query

Code:
SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete]
FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;

Within the Query Open Issues I have the FY broken up

Code:
fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))

View 4 Replies View Related

Lookup Based On Multiple Boxes

Oct 26, 2005

I apologize if this has been adressed. I searched but did not find it. Partially I think because it won't let me use the word 'two' in the search. That's kinda important in this case.

I know how to make a lookup combo box. I used the wizard and copied and pasted the code, actually. What I need is the code to base the lookup from two combos.

For example, the records in my table contain information about different districts on certain dates.

I'd like to lookup the record that matches both the Report Date combo and the District combo.

Thanks in advance.

View 12 Replies View Related

Newb Question - Query Based On A Lookup?

Oct 3, 2007

Hi. I'm quite poor with Access, so sorry if this sounds dumb.

I have a table of customer records (tbl_customers), including name, address & postcode etc

I have a spreadsheet of customers their postcodes from a different system that shows customers who have bought a particular product.

Is there a way that I can create a query that lists those records in tbl_customers whose postcode is matched in the spreadsheet?

Jim

View 3 Replies View Related

Tables :: Lookup Table Based On Relationship

Nov 25, 2013

I attached a screenshot with notes that describes my problem.

View 2 Replies View Related

Update Query Based On Lookup Tables Values

May 30, 2005

Hi, not sure if this is possible or the most economical approach but here goes:

I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.

I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.

Can this be done?!

Thanks in advance

View 2 Replies View Related

Database Update Of Multiple Fields Based In Lookup

Oct 28, 2014

I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.

View 1 Replies View Related

Automatically Populating A Table Field Based On LOOKUP Value?

Nov 15, 2013

I have two tables, "Membership" and "Class." The Class table has a class ID, class name, and cost of the class.

In the Membership table, I have a field to accept a class ID that is entered by a user. I also have a column called Tuition. When the user enters a classID in the Membership table, I would like the dollar amount associated with that classID to automatically appear in the field called Tuition in the Membership table.

I tried the LOOKUP data type and calculated fields, but no luck. I know that DLOOKUP can be used on forms, but I don't see how it can be used directly with tables.

Is this even possible?

View 1 Replies View Related

Tables :: Run Lookup With Unique Criteria Based Off Each Individual Record

Aug 31, 2013

View 6 Replies View Related

Tables :: Database To Track Hardware Assets - Lookup Based On Value

Dec 19, 2013

I am attempting to develop a DB to track hardware assets, e.g., servers, switches, storage devices, etc... I am at the very beginning of the development/design process. I haven't gone beyond the paper stage yet. I am trying to figure out how I would set up the main Asset table so that when I put in a particular vendor, the model field will list models only for that specific vendor. i.e., when I put in IBM in the Vendor field I will only see IBM models in a drop down for the Vendor_Model field.

I'm pretty sure this will involve a Lookup field. But, how do I restrict the lookup of models. Would that be with a query?

View 2 Replies View Related

General :: Creating Entries In A Table Based On Multivalue Lookup

Aug 3, 2014

I am designing a database for a quarry for maintenance of their machines. I have a table that has a full list of all the parts on the machines, and a multiple value lookup field that says what machines said parts are used on. I am trying to have these values translate to another table, so I can then insert a field to say how many of each part are used on the respective machine, to display on an information form for each machine.

I would also like this table to update if the relevant information is updated, for example if a new machine is input, then have the table update to reflect what parts are used on it (an After Update function?) I have made a query that gives me the read out I want (attached) but just cannot figure out how to get that into a table so I can add the extra information.

View 1 Replies View Related