Tables :: Automatic Reference Numbers That Can Serve As Primary Key
Oct 6, 2014How can i generate automatic reference numbers that can serve as primary key.
I want the prefix to include year. Eg Tr/yyy/incremental number...
ADVERTISEMENT
Tables :: Come Up With Automatic Line Numbers For Each Specific Order?
Oct 2, 2014I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
View 14 Replies View RelatedAutomatic Rounding Up Of Numbers
Feb 17, 2006when enetering data into a field on a form it automatically rounds it up the number up to the nearest whole number. this is really frustrating, does anyone know how i can solve this problem?
thanks
Linked Reference Numbers
Mar 30, 2005Hi there,
I Applogies if this has already been asked. I've tried searching for this, but unfortunately I'm not quiet sure what I'm looking for. Problem is this:
I have a database for recording exercises, their issues and recommendations.
what i'd like is if i enter eg: exercise1, then issue1 with recommendatio1 can the reference numbers be linked with one another so that when i enter an issue for exercise2 the issue numbering starts over at 1 again.
it would be the same with the issues & recommendations. each time i enter a recommendation, if its a new issue the numbering is to start at 1 again.
and for this to happen automatically (if i didnt mention this before).
Unique Reference Numbers?
Jul 4, 2005Hey guys,
Hope you can help with this one. I have a form called "frmPatientID", based on a query "qryPatientID" and table "tblPatientID".
I have a field called HospitalNo that is set to "text" and contains the patient's unique hosptal number of 123456, 008777, etc.
However, although the above numbers have to be unique, if the patient is out of the area they get a number of 000000 and this is not unique (i.e. more than one patient can be out of the hospitals area).
So how do I get the database to control this via a rule? (As in everything NOT 000000 needs to be unique). Is it on the form on an AfterUpddate or can I do it in the table?
Many thanks,
Rusty
:D
Modules & VBA :: Assigning Reference Numbers To Records Where Same Customer Appear
Nov 21, 2013I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).
A customer may appear in multiple records (having multiple accounts), but will only receive one notification.
What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.
So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.
The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.
Primary Key Auto Numbers
May 21, 2007I have used priamry keys in my tables and these are set to auto numbers on the advise of fellow forumites.
One question if at the end of every year I need to upload batches of data to my tables, this would mean I would need to go through each record and manually enter the foreign keys in the linked table.
Surely Access has a better way of dealing with this issue?
Queries :: Adding Sequential Numbers To Generate A Unique Reference Number
Oct 5, 2013Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
DateReceived - ReferenceNo
051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001
One Form Field Serve Two Jobs?
Apr 17, 2006Hello all, I have an Access database for a project I'm currently working on.
It's a simple stock control system which records sales and the level of stock.
How can I set up a form that when you click a button, two queries are run?
I want to add a record to the 'Sales' table but also modify the 'Stock' table to edit the 'Current Stock' field.
Any ideas?
Thanks
Tables :: Automatic Refresh Of Linked Tables - ODBC Connection
Jul 22, 2015I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function
Tables :: Master Database - Several Tables With Two Reference Points
Jul 4, 2013I have broken up by master database, table, into several tables. They all share the same ID values since they came from the master table. So each table that I split off has a unique, matching, identifier, ID, in both the master table and the new table. Plus a previous matching identifier listed immediately below.
Example: Permit the master or main table and Builder a supporting table. There are five tables that I split off and all have an identifier similar to the two listed below.
Permit->ID
Permit->BLD
Builder->ID
Builder->BLD
All my tables have the same ID. But each table also has a identification numbers such as builder BLD, Subdivision SB and inspector INS>
Permit has the BLD, SB and INS
Builder has only one occurrence for each builder in the Permit table. As in the past it was identified by BLD number and now also ID. the use of BLD is from when I used visual dBase. The big difference here is that I have two ways to identify one is ID any other is by BLD, etc. My first thought is to identified by the BLD. I don't know why, but I guess I fear using a single "ID" to identify my various split off tables unique value or BLD.
Tables :: Keep Primary Key Consistent As Data Saved In Other Tables Will Use Field
May 1, 2014i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
Automatic Loggin For Liked Tables
Mar 23, 2007I have programed an access aplication that downloads some data from ORACLE, to do so it used some linked tables, my problem is that the first time that the user runs the 'insert into local_table (colum_1) select column_1 fom linked_table' access displays the 'Microsoft ODBC for ORACLE Connect' form, I'd like to control this conection programatically and not to show this window. ¿can this be done?
thanks on advance
Tables :: Automatic Increment And Reset?
Apr 27, 2015Currently I'm working on an invoicing database in which I can register my customers (or partners) and also make invoices and purchase orders. So far, it does exactly what I want it to do, but I'm struggling with the invoice numbers. What I want to have is that whenever I make a new record, the next invoice number in sequence is automatically assigned to it. Another desire is that this number is prefixed with the current year, and the counter should reset with every new year. So, for example:
2015001
2015002
2015003
etc.
Then
2016001
2016002
etc.
Don't worry about the maximum of 999 invoices, because I won't ever go beyond that limit.
Tables :: Look Up Reference For Table Field
Dec 5, 2013When entering a lookup reference to a table field it pop up message saying, "you can't modify the table 1 this time as it is in use by other person or process" i have closed all forms and table reports but the problem is same to rectify ? also when going to see the realationship then there shows no relation ship developed even after the lookup reference from one table filed to another table field.
View 1 Replies View RelatedTables :: Reference A Table Field
Apr 10, 2013I have two tables, Table 1 and Table 2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then..And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then..Is there a way to reference the Table instead of the Form in both the VBA and the query? The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).
View 5 Replies View RelatedUsing Dlookup To Cross Reference Between Two Tables
Aug 13, 2012I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill
COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C
I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
Tables :: Linking Tables With No Common Data And Different Numbers Of Observations?
Apr 15, 2014I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1
Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
Tables :: Combobox Automatic Selection Of Condition
Oct 1, 2012In a form I have a combobox(2 column and 8 row)
I would like that when the user opens the form and adds a new record the combobox showed the row immediately following the condition defined in the previous record.
For example.
First time that I open the form I select the combobox the condition A.
Second time that I open the form the combobox automatically selects the condition B
Third time that I open the form then the combobox automatically show me the condition 3
Tables :: Autonumber / Customer Reference Field?
Sep 30, 2014use 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...
Tables :: How To Use Countif And Column Reference In Access
Feb 3, 2015Basically I have this formula in excel and i'm trying to recreate it in access but i can't quite figure it out.
****a***********b**********c*********d*********e** ******f
3**Car***Cost Center****start****combining*****end*****miles
4**001*****aaa********021****=(A4&" "&C4)***030
5**002*****bbb********030****=(A5&" "&C5)***035
6**002*****aaa********035****=(A6&" "&C6)***040
f4: =IF(COUNTIF($D4:$D600,D4)=0,(E4-C4),(E4-C4)/COUNTIF($D4:$D600,D4))
f5: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))
f6: =IF(COUNTIF($D5:$D601,D5)=0,(E5-C5),(E5-C5)/COUNTIF($D5:$D601,D5))
So basically: If a car is using multiple cost centers for a trip it divides the total mileage among the cost center for said trip provided that the user enters the same start and end mileage on all the cost centers used. And if it doesn't have multiple cost centers it provides the appropriate amount.
=IF(COUNTIF($D4:$D600,D4)
=IF(COUNTIF($D4:$D600,D5)
=IF(COUNTIF($D4:$D600,D6)
and so on
Any way to reference a column in access while referencing a specific point within that column.
Tables :: Source / Reference Of Different Data Points
Apr 24, 2015I want the user to be able to see the source/reference of (many of) the different data points in my database, so I need to store sources/references in the database itself. I want to have all references in one single/common table (list of references). Each reference may be the source of many data points, in different columns in a given table, and in different tables. Is this possible, and can it be done in an elegant way?
View 2 Replies View RelatedTables :: Record Makes Undesired Automatic Change
Mar 4, 2014I have a training database that I have set up and utilize to track attendee data and who is registered to which course.I have a course registration table that has a course title field which is a drop down list populated by course titles from the courses table, and a trainee id field.
For whatever reason, when I view reports or queries that draw from this table, access will make changes to the first record in the course registration table course title field i.e. it will take whoever is in the first record, and change the course they were registered to.
Tables :: Automatic Numbering System - Identifier Of Task
Jul 30, 2013I like to have a Auto number field in my table (tbltask) that use below codification
B10000001
Where B = Base code and 000001 = Identifier of the task.
The identifier of the task is a unique 7 digits number
Tables :: Viewing Text In Tables And Not Numbers
Feb 24, 2015I know this is an Access 101 question, but what is the best way to view text rather than numbers in my tables. In an example which I have attached, an Orders table shows the Customer field as a number. I would like to view the actual customer name when I am looking at the table.
View 3 Replies View RelatedAutonumber-primary Key Across 2 Tables
Feb 1, 2008Hi,
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