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 Replies
ADVERTISEMENT
Dec 14, 2007
Hello all
I have a table containing the following fields:
Customer ID
Customer Name
Countries
The ID is numeric, the name is a string and the countries are the tricky bit since it contains a string of concatenated 2 digit ISO codes: "USCAGB".
What I would like to do is convert the concatenated representation to a normalized one. Take this example table:
ID Name Countries
2 John SEDK
3 Maria USCAGB
From the above table I would like to create a new table that links Customer IDs to single countries as
ID Name Countries
2 John SE
2 John DK
3 Maria US
3 Maria CA
3 Maria GB
How would I go about solving this?
View 4 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Aug 9, 2013
I inherited a spreadsheet that uses an Index (Match formulate to return a value. I want to use Access to manage the data going forward. I am just starting to build my tables in Access so I have some flexibility and want to do it right. Here is a sample of the data:
KeyEnroll JanFebMarAprMay
abc1/2013103521
def3/201300789
ghi5/201300008
So if Enroll date starts in Jan I want the 10 amt returned from the Jan column. If the Enroll date starts in March, for that client I want the March amy returned, etc.
results: for client "DEF" the amt returned should be "7" because their enrollment date is 3/2013.
View 4 Replies
View Related
Jun 6, 2007
Hello
I have two tables with names of people in different forms.
table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname
table2: [surname]
I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]
I've tried using
Like "*"&[surname]&"*"
This returns everything rather than the similar names.
I think this should be simple but can't work it out.
View 1 Replies
View Related
Feb 15, 2015
When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.
So basically I want to go something like this:
.tblDirctoryLocation
txtDirectory
.tblProduct
txtProductName
txtProductNumber
txtProductCADNo
These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.
Somehow I want the db to assemble file location like this:
“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf
Is there any way this could be done?
View 1 Replies
View Related
Mar 3, 2015
I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.
The response I got on another forum is as follows:
-The impression I'm getting is that you've got a database of information over here.
-And it lists the file name, but not location.
-And then you have a physical folder structure with the fax files in it.
-And there are folders for done, awaiting approval, or processing.
-So what you're looking for is something to read those folders and update the status column based on which folder it is in?
-So at the start of the day we have:
New
-Fax1.tiff
-Fax2.tiff
-Fax3.tiff
-Fax4.tiff
Finished
-
In progress
-
And at the end of the day the folders look like this:
New
-Fax1.tiff
Finished
-Fax3.tiff
-Fax4.tiff
In progress
-Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status. Create two separate functions, one for in progress, one for completed and call them one after the other. There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.
View 3 Replies
View Related
Jan 27, 2005
I have a FE database with all the forms, reports, etc. and my BE DB with all the tables is in the same directory as the FE. My problem is when users are not in the office and opens the FE data base they get an error that the path to the linked tables are not available.
My question: Is there a way to make these FE links to the BE dynamic. I really don't want various FE with paths linked to the BE.
View 2 Replies
View Related
Jul 7, 2014
I am making a database, but lets say I have 12 locations and have say between 2-100 named people at each location.
I want to be able to link 2 databases together.
I can use lookup wizard to select either a location, but I also want access to display the names at ONLY the location previously selected (IE: Paul and Bert are in Brazil, Steve and Carl are in Cuba, I want once Brazil is selected only Paul and Bert to appear as options). I'm sure it's fairly basic but a having a little trouble with it, and not knowing the technical terms is making it hard to look up.
View 1 Replies
View Related
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 2 Replies
View Related
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
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
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
Mar 17, 2014
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
View 1 Replies
View Related
Oct 12, 2007
I have a MAIN table with autonumbering for the project number. In a table related to the MAIN table I have a project number that I want to be updated when the MAIN table has a new project entered. Can I keep these in sinc automatically?
View 8 Replies
View Related
Feb 1, 2008
Hi,
I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.
e.g.
job type 1 - autonumbers - 1,2,3,4,6,7,9,10
job type 2 - autonumbers - 5,8,11,12
is there anyway of achieving this?
Any help greatly appreciated
View 5 Replies
View Related
Jan 5, 2007
OK, My main table has an autonumber field in it to make records unique.
At the moment, the number 5-34 are all in use on active records. But when I create a new record either in the front end form, or the back end directly in the table, it attempts to start the record off with an already existing number.
No idea why this is doing this at all.
Is it possible to reset the autonumber to say 50 so all new records start at 50, then 51 etc.
I really dont want to lose the numbers already assigned, as there are other tables that rely on that autonumber as the clients ID to record all the other data.
Its probably something very simple, but I cant carry on with my data entry at the moment.
View 3 Replies
View Related
Mar 13, 2006
I have three tables.
EAch has a field called ID that is an Autonumber that is the primary key.
When I enter data via a form each record has a different ID across the three tables.
Does this mean they are not linking up correctly?
I have attached my databaset if that helps.
Thanks.
View 1 Replies
View Related
Nov 11, 2014
I made a simple access project and I want to make a count for my visits, this counter MUST starts from one each morning "every new day", I use autonumber field to give each visit its unique code, I'm ok with that, but i need additional counter that resets each new day,, i watched some videos for making the autonumber starts from different number like 1000 for example using Append query but i didn't figure out how to use this method for resetting counter everyday.
View 6 Replies
View Related
Aug 20, 2013
A slight flaw in the design requires this change. What is the best way to do it?
View 8 Replies
View Related
Dec 1, 2014
My database exports data to one of our customers who then inputs the data into their system.
One field i have is "RO Number" which i have as an autonumber which works for the purposes of my part of the system.
However they have it set as "RO9999", (Their system has been set up for years, we are a new "branch") but they aren't in access.
They want the RO number to be a running serial from RO30000, RO30001 etc
The data in entered on a form.
View 3 Replies
View Related
May 31, 2013
Why one would want a unique index on an ID key ? It can't be effectively used in joins and is never used in a where clause. So why have one ?
View 14 Replies
View Related
Sep 2, 2013
I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.
View 1 Replies
View Related
Jul 8, 2014
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
View 7 Replies
View Related
May 30, 2014
I need to restrict the random autonumber feature to 10 digits and a positive number for a primary key. I work in the medical field, and HIPAA privacy regulations require the use of random ID numbers to identify records. Also, I need the numbers to be positive numbers because we will be using barcode scanning equipment and the possibility of truncating the negative identifier creates the possibility (admittedly remote) of having a duplicate PK barcodes. I understand that we cannot change the Access feature, but any VBA code to generate a truly random 10 digit positive number I can use as a PK?
View 4 Replies
View Related
Sep 30, 2014
use an account reference from one field in a table on a second table and add a number after it... e.g.
Table 1 Table 2
PEFA PEFA/001
PEFA PEFA/002
PEFA PEFA/003
PEFA PEFA/004
and so on...
View 2 Replies
View Related
