MS Access Help - Unique IDs
Feb 9, 2006
Hi everyone,
I have over 180,000 records that I need help on. Here are key fields I have:
Students
Address1
City
State
Zip
Year applied
My problem here is that, for example, say these students apply for Financial Aid. Each year a student can apply for aid. However, the data collection never got the people’s SSN, so there is not a unique ID. It only created a unique ID by autonumber only. What makes this complicated is that students sometimes change their names. For example, let’s use Joe Schmoe applied for the following year with these names:
2001: Joe Schmoe
2002: Joey Schmoe
2003: Joseph Schmoe
Now, I could tag it by zip to filter it down, but anyone looking at these record can tell these 3 records are the same person, but Access reads it as 3 different recordsets. I do not want to manually manipulate the data (i.e. make the name congruent OR create a unique ID for these 3 records and joining tables). To manually manipulate all 180,000 records is crazy.
The record of over180,000 came from various sources through excel. The DB was created using only 1 table.
Is there a way to manually manipulate the data in an easy way? Thanks for all your help, guys.
View Replies
ADVERTISEMENT
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
Nov 19, 2007
We are trying to use Access to generate, job, task and craft numbers in Access. Job and task will be unique. Craft numbers can use the auto number function. We need the numbers to be 4 digits for Job and Task.
View 2 Replies
View Related
Feb 21, 2015
I Have one table which have 4 column ,i.eID , NAME, PAN, DOB..in this table same id having diffrent data of Name or PAN or DOB except DOB all are text format when i m trying to select unique id which having more than 1 name it shows 0 data, but table having these,,
View 2 Replies
View Related
Jan 16, 2014
I have a basic query off a currency table :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy
FROM tblCurrencies
ORDER BY tblCurrencies.Ccy;
Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)
As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?
Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.
So I started with the following :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts
FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy;
But this just gives the number of AccountID's per currency (regardless of duplication within them)
I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.
View 14 Replies
View Related
May 29, 2015
I already have created & run an Access database for around 12 users. I now am looking to create a database that can have at least 30 users. Each one with a unique user ID & password (Using a security file ".mdw") Users log into a front end on a network environment. Now...My question...Is 30 to large of a user base for Access in a network environment??
View 6 Replies
View Related
Feb 12, 2013
I would like to automate the current process of sending an e-mail with a PDF attachment that is unique to each recipient; it is their test results. I'm sending an e-mail one at a time (which was fine when the number of folks being tested was small, but now I'm up to approximately 200 e-mails and growing each year)
System Environment:
Windows 7 SP1 (64 bit)
Office 2010 Professional
Access environment and Process:
1. A qry object/letter is built based on 4 tables (test results, result averages, a global employee list and location)
2. The Report has both static and dynamic text. Examples include: "Dear" [qry-Letter. F_name] and Address Information
a. =[qry-Letters.F_Name] & " " & [qry-Letters.L_Name]
b. =[qry-Letters.Position]
c. ="Dept " & Right([qry-Letters.Dept],3) & ", " & [qry-Letters.City]
3. The report also includes the employee's results, the session averages (testing is conducted at multiple locations each occurrence is a session) and companywide averages.
4. Report Process: Run the report, entering primary key Emp_ID, report is populated saved (overwritten with the next Emp_ID and printed as a saved PDF.
5. MS Outlook: create a new e-mail, text body is common to all e-mails e.g. "attached are your test results" the PDF is attached and the e-mail sent to the recipient (the e-mail address follows the corporate standard, =Trim([qry-Letters.F_Name] & "." & [qry-Letters.L_Name] & "@abc.com")
View 3 Replies
View Related
Jul 14, 2005
I am just about to start a new DB, but there are a few things I need to sort before i start.
I am working with a quotation system etc which will require the use of unique IDs...
eg 000123 then 000124 etc, this bit is not a problem.
The thing I would like to know how to do is when someone makes an order, I would like the quotation number to be changed slightly to add more detail.
For eg, If the quotation number that is allocated is 00123 and they then order a Conservatory, I would need to have this 00123 become 00123CNS, but this can be in a seperate field, Which I would need anyway.
The question is, How Would I go about this.
I have a table that describrives the product, TBL_PRODUCTS; ID, Product_Type, Product_ABBR
Product_Type = for e.g Conservatory
Product_ABBR = for e.g CNS
I would need the ref number and product abbr combined to create an order number.
[note] There will be a check box to say of ordered ot not, so I assume there will be an If statement somewhere?
View 2 Replies
View Related
Sep 24, 2005
I've been on other forums with this problem but no one can solve my problem.
I've created a database with 3 tables that are linked:
- tbl_customer
- tbl_rates
- tbl_destination
They have the following fields:
tbl_customer
- cust_id
- customer_code
- first_name
- last_name
- company
tbl_rates
- rates_id
- cust_id
- rates_id
- currency
- pre_rate
- cur_rate
- fut_rate
- comment
- eff_from
tbl_destination
- dest_id
- destination
- destination_code
What I've done is created a form based on the tbl_customer table and used tbl_rates as a sub form. tbl_destination is used to populate a combo box that is situated in the sub form.
Each customer has their own rate sheet. Each rate sheet has different destinations and rates. Rates for the same destination are constantly changing. I need to use this spreadsheet to record the history of every change made to the rates of a particular destination. To do this I've just added the same destination with a new rate.
Now, what I want to do is to create a query that will only show the latest entry of a particular entry.
Eg. tbl_rate
Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from
1 - 1 - £ - 0.01 - 0.03 - increase - 19 Sept 05
2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05
I would want the query to show me the following from the above table:
Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from
2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05
Basically, the query should only show the latest entry which involves Dest_id '1' because there were two entries with the same id.
I've been given advice about this issue but none that actually work.
I would be grateful for any help on this matter.
View 1 Replies
View Related
Nov 2, 2004
So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...
In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).
I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:
1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric
Any ideas?
~Chad
View 4 Replies
View Related
Nov 12, 2004
Hello Everyone,
I've data already entered (access/tables) on two different computers, however for the clientid on one computer has a hypen (-) and on another - because of something they couldn't get the hypen to work - so they used the slash (/) my Q is: 1) can I search and convert these to one, 2) is it wise the have / or - in the id?
View 6 Replies
View Related
May 20, 2005
I am very new to db programing so please forgive for such a simple program.
Here is the deal. I have a db for about 500 students.
I would like the table [Students] to create a unique ID "number" for each student. I would like the number to be based on information from the same table. For example if the student's name is John Doe and his number is 555-1212 then I would like to the id to be JD1212. I plan on using more than name and phone number but this was just to show you, what i am trying to get done.
I can do this using a form and a calculated control, but I can't get the table and form to kiss and make up. I was also told you can't save data from a calculated control in a form to a table and it is gernerally not a good idea, so I was told.
Any help would be appreciated.
Sincerely,
Pastor Nick.
View 3 Replies
View Related
Jul 6, 2005
I have a beginner Question....How can I set an unique Index on a field in a table if all the four fields are using duplicate values?
View 3 Replies
View Related
Jul 31, 2007
I have thought about this for days now (and nights) and I have simplified my problem below. (I even bought a book)
I have three tables: 1, 2 and 3.
Table 1 = a, b and c
Table 2 = x, y and z
Table 3 combines table 1 and table 2; ax, ay, az, bx, by, bz, etc.
Table 1 and Table 2 are both One to Many with Table 3.
The Table 1 field and Table 2 field are a ‘unique’ index combination.
My Problem:
It would be very handy to produce a list of the ‘unique’ index combinations that are not in Table 3. That is, the ‘unique’ index combinations that aren’t used yet.
You could do this by trial and error, but is there a Query you can run that will give you a result that you could then Append?
An Unmatched Query on the Table 3 / Table 1 contents will point out a complete lack of either a, b or c as will the same Query on Table 2 for missing x, y or z.
A query with the two non-linked tables will give every combination, but is this the best way to do it? It works by default when you use it to append table 3, but is it the best way to do it?
View 14 Replies
View Related
Sep 30, 2007
Hi There,
I am setting up a basic Data Licence database. For each new record I enter I want a unique number to appear at the top of my form that can not be changed. I would like the number to be the date it is entered plus the auto number so, YYMMDD-(auto number) eg 071001-1. I'm sure it's something simple but I am only new to access.
Also while I'm here, on my form I have a Product field, how can I make it so I can enter in more then one product?
I would really appreciate any help!
Thank you,
Dane
View 8 Replies
View Related
Apr 14, 2008
Is there a way to format the random auto number to just be 4 digits long? Or does anyone know how I can programatically create an auto unique field? This number has to be stamped on a key so it can't be longer than 4 characters but it has to be unique. Any help would be greatly appreciated.
View 8 Replies
View Related
Apr 18, 2008
Hi all
I have been discussing DB table design with a colleague and we have a difference of opinion with regards to primary key ID fields .
My argument is that they should not be actual data but separate, that is an unique record identifier typically an autonumber type. I'm sure I picked this up from reading Access manuals.
My colleague's argument is: if the data contains a unique field, e.g. a unique project number in a table of projects (no project should be listed twice) then use the project number as the primary key. Her second argument is the byte size of an autonumber is 8 bytes whereas you can use a smaller data type to hold the project number thus saving space.
My opinion is that her first reason is flawed as it assumes no change to how projects are identified and that her second argument is irrelevant in today's Gb and Tb systems.
Without trawling through my Access books and the internet for opinions, I would like to know what your opinions are on this subject. I would appreciate good reasons which ever side of the fence you are on. I'm quite willing to be proved wrong and change my ways.
Cheers
Imperator
View 12 Replies
View Related
Aug 3, 2006
I have a query which displays two fields: client ID and order type. I want to be able to display the client only once per order type. I tried to use GROUP BY order type but that gives me a missing expression message. I also thought of using UNIQUE as YES in qry properties but I'm not sure as to which row that property applies (all of them?). What is the best way to display a unique client in one row and order type in the other? So for example for order type "CASH" I wil then have a list of unique individualIDs.
thanks,
M
View 3 Replies
View Related
Aug 16, 2006
Hi,
I have been trying for the last couple of week or so to get a query working.
Basically the query is used to show Hours worked by Post Code. Everything works fine and the query returns 'Hours Worked By Postcode' and Number of Records that the data was created from. (See Report in Attached DB)
However I have now been asked also to show the number of unique Members who by PCode make up the records.
So the report would look like:
By Post Code (See Report)
Number Of Members - Number Of Records - TotalTime
I am having problems returning the number of Unique Members who make up the data, in the query you will see Test and Test1 where I have tried to implement a unique count with no success.
Any help would be appreciated.
Thanks
Daz......
View 4 Replies
View Related
Mar 1, 2007
I have a query that has an Item, ItemPrice, Allocation, and DatePurchased ordered by DatePurchased with only unique values.
I'm using it for a PO Database, when entering items purchased, I have the Item field populated with a items previously purchased, when the item is selected the Unit Price and Allocation Fields are autofilled in with the most recent values from the PO details table.
My problem is if I purchase 10 of the same Items on 10 different dates then the combo box will show 10 of the same Items, because they are not unique values because of the DatePurchased.
What I want is a query to show one unique Item entry with the most recent Purchase Price. I can get a list of unique Items, by only making a query of those and showing only unique values, but as soon as the date is introduced it no longer works, for obvious reasons.
Any help appreciated,
JOe K.
View 1 Replies
View Related
Sep 20, 2007
Hello,
I noticed my subject has been discussed numerous times in this forum. I must have read nearly all topics, but I simply can't get it to work.
I have a table listing: country | category | article | turnover
I wish to make a query which gives me:
country | sum of turnover | count of (unique) categories used by this country | count of (unique) articles sold by this country.
This way I can evaluate with how many different categories a certain country achieves a certain turnover...
It look so simple but I can't figure it out. I hope I'm clear in my question.
HEEELP!
Cheers,
Stefan
View 2 Replies
View Related
Jan 15, 2008
I have a database to track client activity per session.
Each client has a unique client number but multiple activities.
Therefore, if I have 20 clients in the database and each has 3 activities, I have 60 client numbers, 3 *20 = 60. ( Duplicated)
My elementary problem is how do I display the client activity unduplicated so that my query returns only 1 unique client number per session.
I tried the unique records/values in the property box but still.....
Thanks for your help.
View 1 Replies
View Related
Nov 8, 2005
I've a form in which one of the fields the user enters is supposed to be a unique id. The unique id is an alphanumeric text box.
However, it's possible that another user enters an id that has already been previously used.
I'm thinking that I need to run a Find Duplicates Query with the text box entry and a table that holds all the previously entered ids prior to the form being saved so that I can inform the user of the possible duplication.
My question is are there any ideas on how I can do this or code it?
Thanks
View 2 Replies
View Related
Dec 21, 2005
Hi Guys. I currently have a search function via accountno on my form, once the account num is typed in and entered it will display the record of that account num, here is the code:
Sub Combo64_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[AccountNo] = '" & Me![Combo64] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
I also now need the same function for the ID, but not sure how to do it. thanks.
Mosh
View 2 Replies
View Related
May 11, 2006
Just when i thought the db was finished i decided to let the boss check it.
What we have.
An Employee Database that has employee qualifications that each have a specific run out date. I fill in the database with all correct information the bos checks the db and wants to be able to do the following:
Open up forms as usual cycle through each employee (114) form and be able to write a specific message regarding that employee on that employees form.
Simple i said so i did the following:
On the form i have placed a text box.
THE FLAW
He writes something in the box and it appears on all the forms. WHY?
Not an Insult just my favorite saying!
Keep It Simple Stupid KISS
View 4 Replies
View Related
Sep 2, 2004
I have sort of a complex unique fields question. And I know there are probably quite a few ways to get at the answer, but I'm a novice so the simplest way would be best. I have a table (with over 25M rows, otherwise I'd be doing this in excel) with many columns. One of the columns is employee ID, and another is a date. Each employee ID however can have multiple rows with different dates. As an end product I need to have one row for each employee ID with the most recent date. An idea I had would be to sort by employee ID and then by the date. Then insert a column of if statements that asks if a record has the same employee ID as the record below it, but I don't have any experience with if statements in access (and VERY little SQL experience). But there is probably an easier way. Any help would be appreciated. Thanks!
View 1 Replies
View Related
