How To Create All Unique Values In 4 X 4 Matrix Table

Oct 6, 2012

if I got a 4 x 4 matrix table - 4 rows and 4 columns - MS Access 2007/2010 the values should exist as below with no repetition of any number in any of the cells.

1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16

a number should not repeat in any of the cells.I set a primary key on cloumn 1 and defined unique on all the fileds but that doesn't work since 1 columns does not know what the other cell contains and no relationship exists.

View Replies


ADVERTISEMENT

Queries :: Loop To Create New Tables With Unique Field Values

Sep 20, 2013

I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.

Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c

View 5 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.

The second is Bag Number.

The reference number needs to be in this format: 19C.3.1

Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this.

View 2 Replies View Related

Create New Table With Unique Customer Rows

Mar 6, 2014

I am trying to take a table that looks like this:

Customer Name
Fruit

Customer A
Apples

Customer A
Bananas

Customer B
Pears

Customer B
Apples

Customer C
Pears

And create a table that looks like this:

Customer Name
Apples
Bananas
Pears

Customer A
Apples
Bananas

Customer B
Apples
Pears

Customer C
Pears

I think this is relatively simple but don't know the syntax to create this table.

View 2 Replies View Related

Non-Values & Matrix Linking

Mar 2, 2006

Hey all,

I'm not sure whether I should put this in two separate posts, but here goes...

I have just started constructing a database which has many levels.
Some of the field records may not have an entry (as they may not be known) and calculations must be done between these levels. The bottom level is in the form of matrices and I am unsure of what implications this could cause to implementing them in the calculations.

1) I assume from what I know that non-values (or empty record fields), will not work in queries (i.e. they will not be ignored, they will be given a value such as 0 to be used in the calculation). I therefore ask, is it easier to exclude these non-values from the query - or is it better to assign a default value such as -9999, then filter these values out?
Would it be easier to do this exclusion using a filter, or using code?

2) My second question may not even be possible but...
My calculations will involve using values from matrices. I am not sure of the most effective way of using these values in a query? I think there are several options, of which, these two may be the best options.
- turn a matrix from Excel into a table in Access (will mean lots of records - but can be done)
- link a matrix in Excel to an existing query in Access.

Now, the second option may not be possible, but what I would like to do is link a certain cell from Excel to a certain query - depending on the calculation.
i.e. Cell A4 used in Query1 and Cell A5 used in Query2.

I am sorry that I am so ignorant in these matters, but I have been asked to find out what the best way of approaching this is.

Thankyou all very much for your time, I hope what I am trying to achieve is clear!

Kindest regards,

Matt.

View 3 Replies View Related

Modules & VBA :: Get Number Of Unique Values Without Separating Values That Belong To Same Block?

Dec 5, 2014

I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?

So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.

If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.

For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.

So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.

There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.

A few notes.

1.Getting as close as possible to the requested number is a requirement.

2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).

my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134

[code]....

A few sample outputs

one solution is taking rows 1-6 and 26.

OtherID
11098554
56200936
25138850
56148974
56530104
56148975

Another solution is taking rows 1-4 and 10-14.

OtherID
11098554
56200936
25138850
56024315
56115247
56148974

This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.

View 12 Replies View Related

Tables :: Count Unique Values With Different Values

Feb 7, 2014

i have one table in which ID is Primary ID with Different Values

Like

ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U

it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.

View 8 Replies View Related

Tables :: 2 Table To Generate Separate Matrix Table

Mar 9, 2014

I have 2 tables

- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)

I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.

Simple Example

..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES

I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...

View 4 Replies View Related

Forms :: Combo Box With Different Column Values To Create A Table

Jan 17, 2014

Am I correct in thinking the only way to have a combo box with different values in each column is to create a table and then bind the combo box to the column you are referring to if you want it to represent a value from another.

For example if I have a combo box with the words January, February etc can that combo box have a corresponding month number value in a separate column (this combo would be derived from a list) or would I need to create a table holding both the month names and numbers and then bind to the name value for selection but use the month number value?

The reason I ask is I wasn't sure if for every type of list I wanted with multiple column values, I would need to create a table.

View 2 Replies View Related

Tables :: How To Hold Matrix Data In A Table

Aug 17, 2014

In my DB there is a set of company-running rules that are addressed to different groups within the company, like drivers, bookkeeping, warehouse, electricians etc. Both groups and workers scope and number might change from time to time. I need to make a table that holds which worker belongs to which of these groups (one worker to one or more groups). The easiest way of setting this for the user would be kind of a matrix-look form where lines would hold the name of workers, columns would hold the groups and at the cross points there would be check boxes to set or unset membership. However I can't find the way in what table sturcture this could be utilized.

View 4 Replies View Related

Tables :: Table Structure For Training Matrix

Nov 8, 2014

I am starting out creating a training database to track training needs and expiring training etc.I'm just looking for some tips as to how to structure the tables and relationships..I have an employee table, department table and training type table.However im wondering how i can set up requirments and then match these to check if the person is trained up to date on all required skills?

View 2 Replies View Related

Unique Number Will Not Create

Jan 17, 2007

Hello i want to create a tracking number based on information entered into a form.

So i have my main table called "MainData" and this is in the backend and linked through to the front end where my form is.

I then have a query which is called "calllog" which has a field called "Call Number" inwhich i placed the following

=Year([Date]) & Left([CustomerName],3) & Right([Date Created],2)

This then gives me a unique number. Once i have entered a job in it will generate a number. Now this part works fine but it does not update the Maindata. How can i get the this to transfer the data to the maindata table???

View 6 Replies View Related

Create A Unique Identifier

Aug 22, 2006

Hi All.
Is there a way to index and create a unique identifier using a combination of fields (eg. User# + Provider + Date)?
Thanks.

View 1 Replies View Related

DMax To Create Unique ID

Dec 22, 2012

I have a form with a combobox where I select the Carrier witch then populates a multiselect listbox with all the record from that carrier in the specified date. When I select them and click save I want all the record ID(ConID) saved under the same ID(manifest ID) ie,

1 223
1 225
2 344
2 4444
Ect

It saves it in a table called Manifest With the fields Manifest ID and CONID which is a lookup field to a different table. Here is my current code for the on click command

Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Manifest", dbOpenDynaset)

[Code] ....

Now I've done some searching and I thin dmax is what I want to use.. But I am not sure how to make it work...

View 4 Replies View Related

Modules & VBA :: Training Matrix - Matching Listbox Selections To Table Records

May 6, 2015

I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.

Code:
Name EMP ID OP1 OP2 OP3 OP4 OP5
-----------------------------------------------------------------------------
John Doe 526261 C C C
Bob Doe 555622 C C C
Sheila Doe 066600 C C C

Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.

View 14 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

Forms :: Create A Unique Customer ID

Apr 4, 2013

So I have this field in a form/table called "CustomerID". This is to be an ID number for our customers that includes the first four letters of their last name (LastName field) followed by four digits.

This ID is created when a button is pressed in the form. After that, code needs to grab the first four letters of the last name (LastName field in the form and table) and then go to the Contacts table and see if there is already a CustomerID that starts with those four letters. If there is not, then the CustomerID will be those four letters followed by "0001". If there is, it needs to be those four letter followed by the next number sequence that still does not exist for those four letters.

For example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns negative then
CustomerID assigned = zabo0001

Another example:
First four letters of last name = zabo
Check to see if those four letters exist in CustomerID returns zabo0001 and zabo002 then
CustomerID assigned = zabo0003

View 7 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

Forms :: Create Automatic Unique Number?

Apr 12, 2013

I am trying to create an automatic unique 'number' (actually text) in a form. Here is an example of the format...

1456.R1
1456 is the project number
R stands for revision
1 is the first revision

So, in this database there could be 1456.R2 etc. but there could also be other project numbers, say 2323.R1, 2323. Looking for expression I need to enter to have Access look up the last revision for a specific project and then add 1 to it?

View 10 Replies View Related

Unique Values

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

Unique Values ??

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

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

Unique Values To ComboBox

Oct 25, 2006

Hi guys,

I've tried searching all the threads for an answer to my problem and I can't seem to find it....

Ok I know this should be simple...

I know to use Distinct and DistinctRow to only show unique records in a combobox but all I want to do is that once a selection is chosen in the combobox to add a record to a subform, it can't be selected again.

I know in some cases you do, e.g. items on an invoice sometimes need to be entered more than once, but I'm listing Skills for Employees and I only want a user to enter a Skill once per Employee (although Employees can have many Skills, I just don't want a User to accidentally put the same Skill in twice or more per Employee...)...

What's the best optimized way of achieving this?

Sorry for my newbiness.

dazza61

View 5 Replies View Related

ComboBox Unique Values

Oct 25, 2006

Hi guys,

I've tried searching all the threads for an answer to my problem and I can't seem to find it....

Ok I know this should be simple...

I know to use Distinct and DistinctRow to only show unique records in a combobox but all I want to do is that once a selection is chosen in the combobox to add a record to a subform, it can't be selected again.

I know in some cases you do, e.g. items on an invoice sometimes need to be entered more than once, but I'm listing Skills for Employees and I only want a user to enter a Skill once per Employee (although Employees can have many Skills, I just don't want a User to accidentally put the same Skill in twice or more per Employee...)...

What's the best optimized way of achieving this?

Sorry for my newbiness.

dazza61

View 1 Replies View Related

Extract Unique Values

Feb 17, 2005

I have the following SQL definition:

SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
FROM tblFile_Management_Received;

The results would show as:

ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
111111111 2/16/2005 1:23:19 PM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
222222222 2/16/2005 1:24:49 PM XXXX Yes

I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):

ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes

Can anyone help me with this??

Any help is greatly appreciated.

Thanks

View 4 Replies View Related

Sum Of Double Values With Unique ID?

Mar 6, 2014

I have an file with 500.000 + lines.Each line has an ID with an amount.

For example:

ID Amount
1 2

Often the ID appears with a different amount.

I want to have a cleaned up acces file where each ID only appears once but where the amounts are add up.

For example:

ID Amount
1 2
1 4

Should be in the new file

ID Amount
1 6

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved