Unique ID
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 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
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
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 4 Replies
View Related
Sep 8, 2006
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?
View 1 Replies
View Related
Aug 21, 2005
Hi all,
Got my job database going quite well, at the moment, it generates a job number form the first 4 letters of the customers name, and the ID (autonumber), so for example mr smith's job number would be SMIT0012 (assuming the 12th entry in the database)
Now this works fine, tables are linked and the database access the tables via a mapped network drive for other pc's (2) to use. But the problem is, if the database user were to be at a remote location with a laptop, how could this work? I could use a local copy of the tables but then of course the ID's wouldnt match! Can anyone offer any ideas on this?
Thanks
View 3 Replies
View Related
Oct 18, 2005
Wotcha, folks.
I'm in a right pickle at work at the moment.
For the life of me I can't make an Access table have more than 1 unique field.
For example, I want a company's name and postcode to be treated as unique ONLY when they appear together.
So I can have many companies with the same name and different postcodes, but no doubles of the same company name and same postcode.
Does this make sense? How do I trigger a unique property when two certain fields match a previous entry.
I've searched on the net and in these forums, but i can't find help. Maybe it's the way I'm wording it.
So, sorry if this is incredibly straight forward, and I'm being a noob, but this is rather urgent.
Many thanks.
Jake.
View 5 Replies
View Related
Apr 12, 2006
Firstly, my thanks to everyone who is going to be able to help with my problem, and also my apologies if this thread isn't in the right forum.
Ok, I am trying to construct a database at the moment for a small business. The database holds a number of customer records, and all of the basic design and construction I am skilled enough to write myself.
However, I'm running into major problems whilst trying to tackle a certain task...
The Required Situation
I have a form called Contacts. This form shows all of the customer details, laid out neatly, and draws its information from a table of the same name. This aspect works fine.
Now what I've been asked to look into is the possibility of adding two buttons to this form. Each button, when clicked, will open a pop-up window where extra details, such as a log of received telephone calls, can be entered and searched if necessary. the two buttons should do the same thing where the code is concerned, but store their details separately (one button is for sales calls logged, the other for support calls).
Now it is essential that these records are tied - uniquely - to whichever record the database user was on when they clicked the button.
My Current Thinking
I'm stumped. Well, half stumped. I think that the solution lies in sub-forms, but I'm not nearly skilled enough to actually impliment them (and don't want to waste my time doing so if there's an easier way).
Currently I have two tables, contacts and calls, the former to store the customer information, the latter to store the calls that are entered on this new form.
The form contacts has a button on it which opens the form calls. Now I can go to any record I like, click the button and open the calls form. I can enter details for such, and the record gets saved to my calls table. The problem is that it is not unique. No matter which record I'm on when I open the calls form, the details are always the same.
Yes, I know the absoute easiest way is to simply have a few fields on the main contacts form to log these details, and that it will uniquely tie to that record, but unfortunately that is not a practical solution. These calls may run into the hundreds, and must be easily navigated by the database users.
If I've not been clear enough, I can attach a copy of my database for you guys to take a look at, but what I'm after in the first instance is (a) whether there is an easier way to solve my problem, and, (b) if not, whether I'm right about needing subforms.
View 14 Replies
View Related
Jun 12, 2006
I would like to be able to replicate the unique ID typed on form1 to all subsequent forms without having type it in. Is there a way to do this automatically?
Any help gratefully accepted...JimT
View 4 Replies
View Related
