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?
It may be that there is no unique column in a database. Then it will not be possible to create a Primary key based on a single column.In that situation a COMPOUND KEY has to be prepared by combining two or more columns.how this COMPOUND KEY is prepared.
View 2 Replies View RelatedI wish to designate unique customer codes in my database during data entry.
For example;
Liz Stimp = LS1
Liz Stamp = LS2
Lyne Small = LS3
Lipo Suction = LS4
I have successfully linked the first and surname using a "Left 1st letter" in the expression but I do not understand how to add a 'unique' number to the code to give me the result I am after.
Expression so far is .....Left([First Name],1)+Left([Surname],1)
I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.
How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?
So Query1 returns:
25/08/2014
08/09/2014
15/09/2014
while Query2 returns:
01/09/2014
08/09/2014
15/09/2014
22/09/2014
I want query3 to return:
25/08/2014
01/09/2014
08/09/2014
15/09/2014
22/09/2014
I have read through just about every post on this forum related to "Unrecognized Database errors" and have not found a solution to my direct problem.
Stats:
Database is running Access2K on all machines; db is on network location, no sharing issues or permission issues have been identified, no other databases in this network location have had this problem. also, we have tried compact-repair and also have tried re-creating the database and neither have proved successful.
I can open the database just fine if I am the only one opening it. If someone else tries to open it once I have it opened one of the following occurs:
1. Nothing. Hourglass appears for a split second, then nothing happens.
2. Error message "unrecognized database format "mydb.mdb"" appears. If I click OK and both users close the db, it can be opened just fine again by the first user. It doesn't matterwho opens it first, but the second user gets this message.
Any ideas why this is happening?
Thanks!
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?
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?
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.
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
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.
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 RelatedI 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?
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
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 RelatedHi 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
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
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......
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.
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
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.
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
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
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
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 RelatedHi 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.
I have a database that contains sales order information. A sales order could have multiple records (if that sales order has several different items ordered) or just one record. There is a status column in each record that shows a "C" (for closed). For items that were not delivered in a particular sales order, the status column is left blank.
Here is an example:
Sales Order Item Ordered Status
908111 Coaxial Cable C
908111 Transreceiver C
908111 Connector
908112 Coaxial Cable C
908112 Transreceiver C
908112 Connector C
The above example shows that only 2 items from sales order # 908111 were delivered or "C" but one of them is not. For sales order # 908112, all items were delivered or "C". I want to take all records of sales order # 908111 and show it on a table of partially delivered and all records of sales order # 908112 in delivered table.
I've tried different filtering options and group by but it doesn't seem to be working. Anyone has any ideas?