Tables :: Unique Identifiers With Specific Categories

Oct 19, 2012

I am working in Access 2003.

I have set up a form that fills out my master table. What I am having an issue with is:

I have 6 different categories that different items fall under.

example: Category 58-Wire Rope and Cable
Category 60-Fasteners
Category 93 - Safety

I need each item that falls under any one of these categories to have a unique identifier starting with the category number.

Example: Safety Glass - - - part number 930001
1.5 inch Cable - - part number 580001

I want to be able to select a category from my drop down box on the form and for the part number to auto fill with the next available number in that sequence.

View Replies

Tables :: Structuring Categories And Sub Categories

Dec 27, 2012

Suppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another.Cubes can be either Red or Green, Spheres can be either Blue or Yellow.

I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )

e.g. -- I have several boxes totaling:

Cubes
Red 30
Green 40

Spheres
Blue 10
Yellow 20

My goal is to produce three reports.The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)

The First Report would look like this

Target % - Actual % - Target # - Actual #

Cubes 60% 70% 60 70
Spheres 40% 30% 40 30

The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions.

View 2 Replies View Related

Forms :: Filter Down To Specific Categories In Inventory - Scroll Bar In Continuous Form

Sep 9, 2014

I've got a continuous form that displays my massive 3,000+ inventory, with the option to filter down to specific categories. Some categories have 5 items, others have hundreds. The design I have used makes it look really tacky once I filter down to one of the categories that has 5 items, because there is no scroll bar.

What I've tried using is:

Code:
If Me.RecordsetClone.RecordCount < 10 Then
Me.Scrollbars = 2 'vertical only
Else
Me.Scrollbars = 2
End If

But it still doesn't display correctly (nobar.png). This is probably my biggest headache right now, and every "solution" I've found, doesn't work.

View 11 Replies View Related

Tables :: Query Off Of A Junction Table - Multiple Categories

Sep 26, 2013

So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:

ProjectID
100
Category
Admin
Finance

But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..

View 14 Replies View Related

Find Unique Products To Specific Build ID's

Dec 14, 2006

I have a table with 5 fields:

Build ID Process Product Product_De Operation_

What I am trying to accomplish is as follows:

Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.

How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.

View 1 Replies View Related

Social Security Numbers Used As Identifiers.

Sep 12, 2005

G�day all.

I am posting this on more than one site but that should not matter simply because it asks for an opinion and, as such, your opinion should not be construed or even considered as an absolute answer�valued yes, absolute no.

What issues may emanate from using the US SSN, or equivalent numbers in other countries, as an identifier for unique records in a database?

Legal?
Voluntary?
Not under local database control?
And anything else you may care to mention.

Please, members from foreign countries to the US, you are welcome to add your thoughts here.

And please also, this is not about anything you may wish to take up with the Federal Court, Privy Council, High Court or whatever in the future in your country. It�s about your understanding of the situation as it stands, at the moment, in your country.

That�s about it�what do you think?

The following three links should take you to the other two sites�
(Sounds like a bit of gobldegook but you are already on one of those sites.)

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=3&Number=1001301&page=0&view=collapsed&sb=5&o=&fpart=1
http://www.access-programmers.co.uk/forums/showthread.php?t=93569
http://www.accessvba.com/forum/showthread.php?t=17057

Regards,
Chris.

View 5 Replies View Related

Queries :: Parsing Strings Of Text Between XML Tag Identifiers

Oct 12, 2014

I have a Db with a table imported from sql server. One of the fields contains data wrapped between xml tags. The xml tag identifiers when looked at as columns are not consistently the same tag identifiers but effectively I am parsing the string to provide columns of data from between the tag identifiers.

example:

<T6><A29>Compton Group</A29><A31>CO009</A31><A32>CO161/71</A32><A30>Invu Proposal</A30><A28>05 September 2007</A28><A41>Sales</A41></T6>

I am wondering if the method I am using to deconcatenate the string is the bext way. In my query I reference the left tag value .

TagVal1: ParseTagOpenVal([Attributes],3)

And then I parse the result based on the right hand tag value

Val1: ParseTagCloseVal([TagVal1],1)

I have a couple of modules for parsing the string and I am able to deconcatenate using this method but I am wondering if its the only way or the most efficient?

View 1 Replies View Related

Creating Consecutive Identifiers - Automatic Numbering

Sep 17, 2014

I am trying to create a form that creates an automatic identifier with a value one greater than the previous identifier. I was able to create something that updates in batches of two: That is, I end up with identifiers that go 1, 2, 2, 3, 3, 4, 4, etc. How do I get it to create consecutive identifiers (1, 2, 3, 4, etc.)?

View 2 Replies View Related

About Unique Values In Tables!!!

Oct 4, 2006

Hi,
I have a situation and i do not know how to handle it.
I am developing an application for a lawyer. In the data base i have the following tables: Files, the people table (that are involved in a lawsuit), and other tables. The problem is the following: in the people table, beside the primary key i have another unique element that is social security number.
In the files table beside the primary key another unique element exists: the number and date of the file (File 1 from 15.02.2006). A lawsuit file can have more law terms: today it has one law term and maybe next week another law term so i must have the same file many times in the table with the same code and another date, but i cannot add the same file many times in the data base because a file has data about one person and the social security number of the person is unique.

If anyone can offer me a solution it would be good.
Thank you!

View 2 Replies View Related

Need To Have A Unique Number Across Several Different Tables

Jul 21, 2006

Ok,
Bacically I have 2 different lists (well there will be 7 in total):
tblValves
ValveID
KKS_no
Description
List - specifies thats its in the valves list
TOP_Code

tblSpools (pipe sections)
SpoolID
SpoolTagNo
Description
List - specifies thats its in the spools list
TOP_Code

The reason that I have kept these two in different tables rather than combining them is because the spools table will relate to a few tables that will have nothing to do with valves. For instance the spools table will relate to a table called tblPipeSupports.

The thing is this: I would like if there was a unique number across both tables. Collectively the items in coth tables are called components. Some of the components from either table will have drawing numbers for instance so I would like if I could have a table:
tblDrawings
DrawingID
DrawingTitle
ComponentID (FK to valveID or spoolID)

However this won't work because valveID or SpoolID could have the same numbers. I could use a composit key like SpoolID + List but I think I'm probally making it more complicated than necessary. Also I'll have several tables like the drawing table e.g. some of the components will belong in the testPack table... And I'l have to repeat that List field in each..

So what do I do?

View 3 Replies View Related

Finding Unique Records Between 2 Tables

May 12, 2006

Dear all,

Many thanks for taking the time to read this.

I have a slight problem which seems like it should be easy to solve but I have no idea how to do it.

I have 2 tables (table1 and table2 for instance) Table2 contains more data than table1 but should contain everything in table1 as well as more.

How is it possible to return just records that are in table2 that are not in table1

Eg

Table1 contains:

IDab
112
222
322
422
511
611
711

Table2 contains:

IDab
112
222
322
422
511
611
711
833

The query should return just the line:
833
as this is unique.

thanks again

View 6 Replies View Related

Tables :: How To Create A (unique) Field

Nov 12, 2012

I have a table as followed;

EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK

What I want to do is create a new field that automatically populates based on what is entered into the above fields.

Essentially creating a single field with unique data generated by multiple fields.

For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test

The new field can be generated as followed: JohnPDoeFederalgovTest

What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.

View 5 Replies View Related

Tables :: How To Make The Field Unique

Dec 13, 2013

I have downloaded a contact template from the internet ready to go. There are few things I need to get rid off and make changes to.

One of the most improtant thing is making one of the field unique. For some reason, i can get to the design view. I open navigation panel and right click on the table, there is no such thing call design view on the menu.

View 6 Replies View Related

Tables :: Appending Only Unique Data

Oct 14, 2014

I use Cognos to pull raw data from Oracle. I take this data and put it into Access 2010 tables to then run queries. Because the people creating the data in Oracle are often slow, I run back data to catch missed information, typically two months. For October, for example, I'll run October and also September and August.While I have new data, I also have a great deal of duplicate data. How can I ensure that I append only the new data and exclude the duplicate data?

View 6 Replies View Related

Tables :: Multi-field Unique Key

Feb 8, 2015

I have 4 fields in a table that when combined must be unique.I know you can setup multiple fields as a combined key, but I don't really need these to be the key (I will auto number a unique key). I just need to make sure the same combination of codes is not entered twice.Do I set this up as multiple key fields anyway?

View 1 Replies View Related

Pull Unique Recs From Many-to-Many Related Tables

May 13, 2005

Greetings all! We have a mentor-mentee database that's been setup with a many-to-many design. We're storing essentially the same data in each table except for the field names. We thought we were good to go but one additional request was made to determine a count of employees participating in the program (regardless of whether mentor or mentee) in each section of the company. We don't want each person counted multiple times if they are both a mentor and a mentee, or if they have >1 mentee or >1. I created a couple of queries to pull unique empIDs from each table to narrow down each table BUT I'm stuck on being able to pull unique id's from both tables.

I first tried the unmatched query wizard from both viewpoints but then I don't get those cases where a mentor is also a mentee. In other attempts, I end up with a cartesian product. I've cruised through the queries section but haven't come up with any examples related to this type of situation.

I need a way to pull both unmatched records from each table while also getting essentially the first occurrence of instances where mentorID=MenteeId. I'm no SQL expert so if someone can think of a statement that would suffice, it would be greatly appreciated.

View 2 Replies View Related

Tables :: Combining Fields To Create Unique ID

Nov 14, 2013

How i would best combine values in a table to produce a 'primary key id number.'

For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233

I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.

View 14 Replies View Related

Tables :: Creating A Unique Invoicing Number?

Aug 19, 2013

What I have is a small POS system I'm trying to create a unique invoice number for each transaction (multiple records)

I have two tables, one named "CurrentTransactions" and one named "Past Transactions".

I have everything working except the invoice number my system works by adding pre defined records into the table "CurrentTransactions" and once the sale has been finalised it transfers the data to the "PastTransactions" table.

take the last number from the "PastTransactions"."InvoiceNumber" to create an invoice number in the "CurrentTransactions", and I want to be able to do it using an expression in the default value property as opposed to in the form ( I've tried the form way but using Dmax would require me to almost rebuild my entire POS system )

View 5 Replies View Related

Tables :: Unique Identifier Over Two Fields On A Table?

Jan 22, 2015

Any way on a table that I can have a unique identifier over two fields? E.g.I have a login ID and a Domain field in a table with sample data below.

Login Domain

John Test
Jane Test
Fred Live
John Live

The login names need to be unique to the domain so the integrity cannot be breached. This has to fit into one table (although the domain is actually being pulled from a look up table so is in fact a numerical value) for ease of form creation.

View 2 Replies View Related

Queries :: How To Join Tables With No Matching - Unique Fields

Jun 16, 2013

I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).

I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.

View 8 Replies View Related

Tables :: Unique Identifier - Increment For Duplicate Records

Jul 10, 2014

I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.

What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.

What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.

An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.

View 5 Replies View Related

Tables :: Unique Multicolumn - Avoid Duplicate Records

May 28, 2014

I have 3 tables

tbl1 :
NameID
Name

tbl2 :
CatID
Category

tbl3 :
ProdID
CatID
NameID
Remarks

How can I avoid duplicate records in tbl3? Since remarks can be Null? product is later used in tblInventory...

View 1 Replies View Related

Combine Multiple Tables (all Are With Unique ID Names) Into One Table?

Nov 22, 2013

I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.

View 3 Replies View Related

Tables :: Unique Record Entry - Index Or Primary Key Cannot Contain A Null Value

Dec 6, 2012

I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:

It suggests that I create multiple primary keys in the table

When I do it, it comes back with an error: Index or primary key cannot contain a null value.

View 6 Replies View Related

Tables :: Run Lookup With Unique Criteria Based Off Each Individual Record

Aug 31, 2013

View 6 Replies View Related

Tables :: Spreadsheet Weekly Upload - Setting Up Unique ID On Import

May 13, 2014

I have a user that uploads a spreadsheet weekly. I want to assign a unique id to each record that is the current date plus the start and end date for the data they are loading which would be the week prior plus a counter.

For example: Data from the week of 5/4-5/10 is loaded on 5/12.

Record one would be 050414-051014-1
Record two would be 050414-051014-2
Record three would be 050414-051014-3

and so on. How do I accomplish this in my table design? They will be deleting the prior week data and pasting the new data so the table design will not change.

View 2 Replies View Related