Hey, I have two tables, one with order information and another with client details. The primary key in each of them is a 'ClientID' field. As well as the two tables, I have forms letting you enter data into each table. On the booking form i want to be able to look up the Client ID based on a customers surname. Can I do this?
View RepliesBasically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
View 8 Replies View RelatedI am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
i would like to sum all the column fields in a tables column where an id is the same as the id in that table.
I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
I work with Access 2010
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
View 5 Replies View RelatedI have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.
How can I join these tables in a 3rd table with all the fields from both tables.?
I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:
1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]
I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:
a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.
I have two tables called Hardware and Desk. They both have two fields called ChangeID and Priority.
What I would like to do is set it so that if we change the Priority in a record existing/created in 'Hardware', the Priority of the related record in 'Desk', also changes.
How can I achieve this?
I have a database with various tables, customers, jobs_table, black_book. I am wanting to have a field that looks up a latest date.
Both the [jobs_table] and [black_book] have a date field, there can be multiple entries per customer in both fields. What I want to do is have 2 fields in the [customer] table that looks up the customer name in [jobs_table] and [black_book] and brings back the latest date.
Helllo,
I have a database with few tables but I'm having a problem with using different fields from different tables.
When I try to set a Validation Rule for Date field in Tbl1 to be Between Date2 And Date3 from Tbl2, I use the following expression:
Between [Tbl2]![Date2] And [Tbl2]![Date3]
I place this in the validation Rule field in the Table edit, but I always get error "no such field..." :mad: what's the matter?
Also when I want to set a default value for a field that's calculated from different fields from different tables, it always shows me an error about "automation object"
Your help is very appreciated
I am extreme noob at access. Ill just toss that out right now. I am reading as much as I can, but with the wealth of info out there, there is still a disconnect with my style of learning. I am trying to create a simple inventory database for my work. I have looked at the inventory templates and they overwhelm me. Way to complex at the moment for me trying to understand this. So i set out to learn and build and grow my database functionality as I go along.
With that, I have a question. I came across some reading material that suggested it is possible to have multiple fields as a primary key. Allowing you to have duplicates in each field, but no duplicates across the entire group of fields.They didnt explain it much, but I gathered it to mean as much with Field 1 and 2 as PK's
Code:
Field 1 | Field 2
1 2
2 1
1 3
1 2 -----> This would be a duplicate of the first
Is this correct?I have not found out how to make multiple PK's though. How is this type of behavior achieved?
I am building a contacts database - brief structure as below:
tblOrganisation - details pertaining to the company
tblContact - Primary key is ContactID (autonumber) and details about the contact
tblContactDetails - Primary Key is DetailsID (Autonumber), Foreign Key of ContactID and fields to store various sets of details.
What I want to do is each time a contact is added I want to create an entry in tblContactDetails with their ContactID as the foreign key. I have created a form based on tblOrganisation, with a subform in Datasheet view showing the contact info (Name etc) which gives the + symbol to access the contact details. Trouble is if there is not an already entry in tblContactDetails with the same ContactID then there is no way I can add details.
I have a field1 in table1 & field2 in table2. (field1&2 are numbers)
After designing the tables I linked them with an ID (which is primary key in table1).
When I created a tabular form based on these two tables & tried to enter data, two problems.
1) Error come unable to update / enter & help says its locked while when I check the property of Locked, it is set as 'No' & Enabled 'Yes'. Why I can't input data.
2) The calculation field2/field1*1000 does not appear in a textbox (where it is defined).
Nafees
nafeesjan@msn.com
Anyone can help me how to link fields in different tables. Im creating a databse that has an ID. and that ID will be same with the ID on the other table.
Table 1
ID = 123
Table 2
ID = link to (Table 1 ID) and when i input characters on the Table 1 ID field it will appear or inputed also at Table 2 ID field.
Hope some ideas. Thanks
Michael
I have an Access 2000 database with several tables in it. Against each field in each table I would like to set every REQUIRED and INDEXED value to NO. Is there a way I can do this using VBA?
If it helps, one of the tables has 2 fields in it named TABLE NAME and FIELD NAME with every field against every table listed.
Any help most appreciated.
Dalien51
I am a firefighter who has been tasked with creating a database to track repairs and inventory on breathing apparatus(SCBA). There are several(5) related pieces of equipment that I think should go on the same table. Three of these will have nearly identical fields. The other two will have additional field specific to them. Should I create one table including all five pieces and just leave the extras fields blank or is there a better way.
Thanks.
Mike
Hi,
it's been a good few years since i have used Access and my memory seems to be failing me hope some one can help:
im trying to set a feild that Selects a value from a dropdown box with a set of values , whihc is defined by the results of a previous field selection.
i.e i select an employee from drop down list the next field is the National insurance number, which i want to auto populate based on the answer from the previous table.
also along the same route im trying to make a similar table that when you pick a particular brand of motor car it will populate the next drop down field/box based on the models avalible to that brand.
in both cases i have seperate tables, i presume that i need to make some quieries and some vba code to allow the correct selection but i really don't know where to start.
oh the joys of trying to make p11'd easier
thanks
slack
I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
Thanks for any ideas
hi all
i can do simple additions in access but i have been asked to calculate fields in different tables
all i need to do is
i have 2 tables in my database
Table 1:main_database
Table 2:Security 2_3
and i have in table 1 got 1 field called Estimated security value
and in table 2 i have 2 fields called Sec 2 estimated value and Sec 3 estimated value
all i need to do is add all of the fields together in query or field in a form
i have tried =([Estimated security value]+[Security 2 & 3]![Sec 2 estimated value])+[Security 2 & 3]![Sec 3 estimated value]
but i get '#name error
thanks in advance
chris
In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I just cannot figure out how to do this.
Any ideas are greatly appreciated
/twallstr
Hi
Can you tell me if the following is possible and if so how to do it.
I have several different tables which require a date to be entered.
99% of the time this date is the same ie
(Table 1) Visit Date = (Table 2) Scan date 1
(Table 1) Visit Date = (Table 3) Scan date 2 etc.....
However in some cases Scan date 1 and/or Scan date 2 may not be the same as Visit date so the input does need to be editable.
Is there anyway I can link the information in Table 1 to automatically be displayed and stored in Table 2/3 and to be able to edit Tables 2/3 if the scan date is different to the visit date which it could be for a small number of records.
Is this possible or is it simply impossible to do this.
Thanks for any advice.
Hi, Im new to Access and VB so please be patient with me. Do know a little about sql queries though.
I have two tables. The first (clones) has another table (BLAST Nucleotide) which is dependent on it (ie: one clone can have many BLAST records)
I want to create a search where I can search by either clone name or BLAST name, and return the results in a list box, and then select an entry from the list & view
Any help appreciated
My zip file of my database can be found HERE
I have a table with 3 yes/no fields. I need them to be dependent on each other meaning if one is yes the others can not be yes. How can I set this up?
View 5 Replies View Related