General :: Conditional Combination For Data In Tables
Jun 12, 2014
I have specific query related to Access, Currently I'm trying to create all possible combination of lot data. I need to know if this would be possible in MS Access.Below is simple example of what is required
Table1 Table2 Table3
Value1 TokenW GateA
Value2 TokenX GateB
Value3 TokenY GateC
Value4 TokenZ GateD
I have 3 sets of data for which I've to create all the possible combinations but with restrictions.Below are few of my restrictions
1. "Value2" always comes with "GateC" & Vice versa but, both don't exist independently.
2. "Value4" always takes "TokenW", But "TokenW" exists independtly as well doesn't need Value4 always.
View Replies
ADVERTISEMENT
Feb 22, 2005
Hi All
Has anyone experienced this:
I have a datasheet as a sub-form on a main form. Because the main form is accessed from 2 different forms in the system (let's call them A and B), I hide a column on the datasheet that is not relevant when the access is from Form B. This was working fine.
Until I added a new field to the datasheet/subform with some conditional formatting (background colour change dependent on field content). Now the hide column function causes an error which locks the database. I removed the conditional formatting and the hide column functions works fine. Please note the column being hidden and the column with the conditional formatting are not the same column.
Seems the combination of hide column AND conditional formatting in the same datasheet is the problem.
Any ideas?
Cheers
Alan
View 1 Replies
View Related
Oct 25, 2014
I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.
I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)
Some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.
however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.
To explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.
How to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?
View 14 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
View 7 Replies
View Related
Nov 22, 2006
Hi all
I have a table with data like
Field 1 = Ref_No (IM1006/0548)
Field 2 = Loc (CBUS)
I want to combine these two fields in a separate field
Must look like this
Field LocNo = CBUS0548
View 2 Replies
View Related
Nov 4, 2014
1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
How can I do this?
View 3 Replies
View Related
May 16, 2013
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
View 2 Replies
View Related
Aug 1, 2012
I have an Access 2007 database with two tables (I will call them table 1 and table 2)
Both tables contain the same two fields. (I will call the FirstName and LastName)
Table 1 has an associated Form where the user enters the two names. When the value in either one of the two fields in Table 1 change I want the corresponding field in Table 2 to automatically update with the same value that were entered in Table 1. Basically I want Table 2 to automatically replicate the same data in Table 1. So if I type the text "John" into the FirstName field in Table 1 then the FirstName field in Table 2 will automatically update with the text "John"
I am new to access and am struggling with the automatic updating.
If the automatic part is too hard then I will be happy to attach the update action to a command button.
I have uploaded my database file with the two tables for reference. I want to get the fields (for all records) in table 2 to replicate table 1 so that when table 1 updates table 2 values changes to show the same text.
View 3 Replies
View Related
Nov 18, 2014
I have a database with numerous tables. I have set up relationships between all tables (they are all pretty one-to-many relationships). I then created forms in which to enter and view data. Data entry is fine; however, when I go into my tables, I realize that it is not storing the ID# for related tables in the main table.
For example - I have a table called "ContractTypes" with a field for ID#. That table is linked to the main table called "Contracts. In the form, I have the ContractType as a lookup field, I select the one I want for this entry, but it is not storing that ContractTypeID into the Contracts table. Also, when I go back into the form to enter a new record, the data on the subforms is from the previous record and not the record I'm currently working on. In other words, it does not appear to be linking the ID#s from the different tables.
View 5 Replies
View Related
Jun 15, 2014
There are 2 tables:
Table 1 has National Insurance Number, first name, last name, phone no, address.
Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
View 10 Replies
View Related
Dec 12, 2012
I have two tables that I'm struggling with how to relate to each other. One is tblHardware which contains all the items that we sell. There are no duplicates in this table. The only columns are a part #, description, long description, and cost.
Then I have another table with is tblPackages. These are the packages we sell which pull from tblHardware. This one just has an autonumber, description, and image (image I'll add later and not worried about now).
What I'm struggling with is how to tie these two together because one Package will contain multiple items from Hardware however there are multiple Packages that have the same Hardware items (17" monitor for example).
View 3 Replies
View Related
Jul 6, 2013
I created a form that allows users to enter data into the fields and populates the table.But can I have it do that and populate instead of one table, two tables?
View 13 Replies
View Related
Jan 31, 2013
I have 2 tables.
Table one is for person ID
Table two is for number of absences
I created a one-to-many relationship with the update/delete option selected. It didn't work when I wanted to add new data into the tables (via form). Then I tried a many-to-many relationship (with a third table) but it doesn't seem logical to do so, as I only need to add particular data into one table, but need a field from the 1st to differentiate records.
(table 1 = one record per person, table 2 = multiple records for 1 person)
View 2 Replies
View Related
Nov 5, 2013
We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
View 9 Replies
View Related
Sep 7, 2012
I work in an office with Office2000. One branch of our non-profit organisation had a volunteer design a database in Access to track the usage of rented storage bins.The database works great for its intended purpose, however the volunteer is not available and the front-end is so locked down that I can't get into the table structure to run some usage queries I need for my own job, which is Resource Development (donations).
I don't know what to say to give more info, because the computer is a stand-alone in a different department detached from the network, so I have to physically go to the other end of the building & work in it, I can't get screencaps etc easily, and most of the menus have been disabled so it doesn't even give me the info about the version of Access etc.
View 6 Replies
View Related
Aug 17, 2014
I have a form which will have many duplicate stock numbers in it , field called " stocknumber"
I also have a field called "qty reqd"
I want to apply a conditional format ( green background)to the "qty" field where the stocknumbers are the same
I tried [stocknumber]= [stocknumber] but did not work
View 6 Replies
View Related
Oct 29, 2013
I am trying to get my expiration date field to highlight a certain color once the date comes closer.I have weapons qualification dates. The user will input the date he/she qualified and then a expiration date (6 months from that day) automatically pops up.
Now what I want to do is once the expiration dates gets closer is change the color. So when it is 90 days from expiration it would highlight yellow, and when its 30 days away it would highlight red. I believe this can all be done through conditional formatting, but for the life of me I cant figure it out!!
View 5 Replies
View Related
Aug 10, 2015
In column D I have a date (different on each row) and in Column E I have another date (again different dates on different rows)
I need Column D conditional formatted to that
If the date in column D is less than Column E then it turns red.
I tried doing one row and it was giving erroneous formatting
Need a lot of rows done.
View 3 Replies
View Related
Jan 3, 2007
Happy new year all. I have 2 tablesI have a query that if any tbl2.num=tbl1.num AND tbl2.action = 'confirmed' this row should be selected for deletion from tbl1. I've tried linking the tables and using an iif statement, but no good and I'm not sure what else to do- any ideas?Forgot to add- tbl1.num might or might not exist on tbl2
View 2 Replies
View Related
Sep 26, 2012
I have a subform based on a query. In that query, I have calculated values for price+tax and another price+discount+tax. Now I have a conditional formatting that if the gross total is not equal to amount due, the amount due box will turn red. However even if they are equal visually, it still turns red. Ex. The gross total is 336.00 and the amount due is 336.00 also, so it should NOT turn red, but it does. When I click on the amount due control, the value turns into 335.999999195337. How do I tell access not to make 336.00 into 335.999999195337.
View 3 Replies
View Related
Jan 14, 2014
I have a field on a form OrderDate and a field DOB. I am not sure how to create an expression in conditional formatting that:
=IF([DOB] - [OrderDate] <30), Then background is red
So if the OrderDate is 1/1/2014 and the DOB is 1/15/2014 then the DOB field turns red
If the OrderDate is 1/1/2014 and the DOB is 2/29/2012 then the DOB field does not turn red.
View 8 Replies
View Related
Dec 11, 2012
I am struggling to format a field on a report.
I believe it is because special charachters are in the text string
I want to highlight the field if it contains the following text
Stage 6: Won and Deploy (shipping)
Regretably, I believe Access see this as code.
I suspect I need to encapsulate it some way, but nothing seems to be working
What am I missing?? How can I force Access to see it as a text string??
View 1 Replies
View Related
Feb 6, 2013
I have tried to change the colour of the text in time fields with no a value of 0:00 by using conditional formatting - to no avail.
View 4 Replies
View Related
Oct 25, 2013
Having recently upgraded to Access 2013 I've noticed when designing forms or reports the Shape Effect button is greyed out. Also, when I click Conditional Formatting no dialogue box pops up. This is happening on all old Databases and new ones created with 2013. (Windows 7 64 bit)...
View 6 Replies
View Related
Jan 30, 2013
I have two tables "Funds" and "Classes" with one-to-many relationship, resp. (through the "Fund_name" fields). There is the field "Active" in the "Funds" table and the field "Open/Closed" in the "Classes" table. What I would like to do is to put "No" value into the "Active" field record if the corresponding (with respect to the relation of course) "Open/Closed" field records have all values "closed". Otherwise put "yes". Is there any way, how I could make this?
View 14 Replies
View Related
Sep 10, 2012
I've created a access database containing multiple tables, theres one main table that will contain all the data compiled.
I then have other tables that contain some matching data but with an additonal column filled in,
I want to be able to pull the data from these other tables into the main table and compile it including merging some data into a single feild.
Effectively this is what the inputs will be:
Code:
Table 1
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008
T0941B1 SLN019149 EFE0008
T0941C1 SLN019149 EFE0008
Code:
Table 2
Ident Number Solution TRN
T0941A1 SLN019149 EFE0167
T0941B1 SLN019149 EFE0167
T0941C1 SLN019149 EFE0167
Code:
Table 3
Ident Number Solution TRN
T0941A1 SLN019149 EFE0188
T0941B1 SLN019149 EFE0188
T0941C1 SLN019149 EFE0188
Then the main table i want to compile the data like this:
Code:
Table 4
Ident Number Solution TRN
T0941A1 SLN019149 EFE0008,TRN0167,EFE0188
T0941B1 SLN019149 EFE0008,TRN0167,EFE0188
T0941C1 SLN019149 EFE0008,TRN0167,EFE0188
i want it to show as 1 line item and compile the TRN numbers into a single feild per row rather than having duplicates.
View 1 Replies
View Related
