Dynamic Values In Tables

Dec 13, 2006

Hello everybody! i want to create a table consisting of 2 columns. one column may contain constant values and the other has to contain dynamic values (values of the second column must depend on the values of the first column). For example if the constant of the first column is ID then the variable of the second column will be ID+5. Something like that below

Column 1 Column 2
ID1 ID1+5
ID2 ID2+5
ID3 ID3+5
. .
. .
. .

Could anyone give me a tip about that? Thank you in advance...

johann

View Replies


ADVERTISEMENT

Creating New Records With Dynamic Default Values

May 31, 2005

Hello all,

I'm relatively new to Access programming, so I am mostly at a loss as to what methods, whether by query or VBA code, to do this. I am building a patient log for medical interns, so that the individual entries would include information of an individual visit to a patient. Part of this would include a function where I could, at one click, create a new record for the specific patient with fields such as patient ID, name, etc already filled in, with the values coming from the record that was open at the time, a sort of "dynamic default" value. I still want to be able to create blank records with the default ">*" button at the bottom of the form, so ideally this would be a new function. Would this be a relatively straightforward task, and if so, could someone give me some basic pointers to it? I'm in the process of reading some VBA self-help books, but have yet to figure it out. I have some experience in C++, so if I were to take the VBA approach, all I would need are the variable names and how to call/access them, and I should be able to knock together a working draft that way. Thanks in advance to all.

View 1 Replies View Related

Tables :: Link Dynamic Data From Web Page

Aug 28, 2013

I want to be able to connect dynamic web data such as live gold price, exchange rates from the relevant web pages to a table in Access 2010. I have searched the forum without finding any related answers.

I understood that this can be done in Excel by using "Get External Data from Web" where the connected cells will be updated along with live data changes in the linked web page. I could set up a link table from Excel in Access but this would be tedious since the Excel file has to be opened to get the data refreshed everytime I open the Access file.

How to load dynamic data directly from web site to an Access table?

View 1 Replies View Related

Modules & VBA :: Dynamic Linking Of FoxPro Tables

Dec 11, 2013

I'm looking for some sample VBA code that dynamically creates a link to Visual FoxPro 9 table. Our group has a number of end user FoxPro applications, and as FoxPro is reaching the end of its life in January 2015, we need to replace it.

A lot of the processing we do uses tables with a date embedded in the name, e.g. MyData_20131211.dbf. We'd like to be able to let our users to use Access queries that point to these tables without having to manually create the ODBC link each day. Is there a way to set up a link once, then use VBA code to dynamically change the table it points to?

For example, we set up an ODBC link table to MyData_20131211.dbf, and rename the link table in Access to MyData_Today. Then tomorrow, the VBA code would change the link to point to MyData_20131212.dbf.

An alternative would be to dynamically recreate the link each day.

As the tables are large, we don't want to import them into Access if we don't have to.

View 3 Replies View Related

Making A Dynamic Report From My Dynamic Form

Jun 30, 2006

I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!

View 6 Replies View Related

Modules & VBA :: Concatenate String In A Loop To Generate A Dynamic SQL To Compare 2 Tables?

Jul 11, 2014

I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want

Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount

[Code] .....

I am trying to achieve something that reads like the following

r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....

View 2 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

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

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

Jul 24, 2007

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

View 4 Replies View Related

Tables :: Passing Field Values Between Tables

Dec 10, 2014

Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.

Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.

The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.

In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.

View 7 Replies View Related

QRY To Get Values From 2 Tables

Sep 26, 2006

Hi all....

I have been beating my head for 2 days on this. Any help will be GREAT!
I have 2 tables: CLIENTS and RELATION

NAMES has the client_ID, first_name and last_name along with other information for clients. RELATION is how the clients fit with each other. RELATION has relation_ID, Relations_description and up to three fields where we can enter the ID of clients from the client table called Rel1_ID, Rel2_ID, and Red3_ID. Now all the three rel_IDs come from CLIENTS and stores only the client_ID. There are multiple relationships.

How can I display the names and not the IDs for the clients?

I am able to display only one client name. I need this for form as well as report. I can do dlookup for form but don't know for reports. I am thinking that if I have it in the query, then I can use that for the form as well as reports.

I am able to show the the name for one client by running the following qry. But I am not able to modify that to show the other 2 names.

SELECT RELATION.Team_Name, RELATION.relations_Description, RELATION.rel1_ID, RELATION.rel2_ID, RELATION.rel3_ID], [client.f_name] & " " & [Client.l_name] AS Client_Name
FROM RELATION, CLIENTS
WHERE ((([relation.rel1_id])=[client.client_id]));

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

Matching Values From 2 Tables

May 4, 2007

I have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon

View 1 Replies View Related

Tables And Calculated Values

Oct 14, 2005

I have been reading previous post s regarding the storing of calculated fields. Basically everybody says don't do it. However, if I don't I am not sure how to achieve what I need to achieve.

I am storing data on tool vibration levels. Data is input for X, Y and Z axes and a vector sum (total) is calculated on the fly using a query. So I only ever store X,Y and Z.

Ultimately I am looking for this database to be able to give me useful statistical outputs in particular a range of vector sums for a tool type. If I don't actually store the vector sum for each test then how I am ever going to report a range? The data would never change for each test as these become statictics, it's not the same as working out rates for example which may vary according to parameters.

What is the best way to do this?

View 2 Replies View Related

Tabstrips And Values From Different Tables

Apr 13, 2005

I havent used Access 2003 much before (or any other version) but i have to produce a database for a vehicle restoration place to hold details about the client, their vehicle, stock etc...

I would like to use a tabstrip for navigating the DB with tabs for each section. E.g. a Client Info tab, a vehicle info tab, one for parts and stock and so on.

I have designed a form with a tabstrip on and am having problems when it comes to showing the information on each page. So far I have text boxes to edit the client details on the first tab page, but then when i come to the second, i am having trouble getting it to display/update information from another table.

Can anyone advise me what i should be doing to make it work, or should i try a different design?

View 6 Replies View Related

Help ! Display Values From Tables!

Sep 7, 2005

Hi guys,
I need urgent help on a simple question. I hope you will be able to help me.
I have a table CustomerMaster which stores customer number and name. (Customermaster)
I have another table which stores the product details for customer. (customerProduct)
I have another table which shall store order details. (CustomerOrders)
On the form, the user selects a customernumber, the system then displays the customer name.
The user then picks up the product ordered by customer (picked from combo box). On the combobox on recordsource I have given a select query which fetches the records matching the customer selected from table
Now I want the Product Price, tax and duty of the product to be displayed for the selected product. Somehow, I have to again make that as a combobox and then select the price (although there is only one record...
Can anyone tell me how to do this?
Regards
K

View 14 Replies View Related

Passing Values Between Tables

Aug 18, 2004

Hello,
I would like to know how to pass a value from one table to another within the same db based on a matching username. I need to update a column called 'store' in one table with the values of another column called 'value' from a different table.
Thanks for any help in advance.

View 5 Replies View Related

Tables :: Accepts Only Certain Values?

Jul 1, 2013

I want my table to accept only certain values in a field. I tried different things with no luck.

Field: OCC (except only 1 or 2)

I tried Validation Rule using Expression Builder, which did not work for me.

I also tried creating a default value of the same field. What I'm trying to do is have 1 come up as a default, and have the ability to change it to 2 if needed. But only have the ability to type in a 1 or 2.

View 6 Replies View Related

1 Combobox With Values From 2 Tables?

Apr 23, 2013

I Have to tables, Employee's & Vehicle's. I'm making a dispatch form. and i want to put a "Unit Dispatched" combo box that will populate from the Employee's ID and the Vehicle ID. We sometimes dispatch employee's to a job on their own and sometimes we send a company vehicle. is there any way to do this?

View 1 Replies View Related

Matching ID Values Between Two Tables

Jun 4, 2014

I have two tables, one has two fields:student ID and student name.its kind of like this:

1 Mark
2 Tom
3 Franklin

the other table has three fields: student name, student classes, it goes like this:

Mark calculus
Mark Biology
Tom Statistics

Franklin Calculus
Tom Chemistry

what I want is for the second table to have its related id from the first table so it could be like this:

1 calculus
1 biology
2 statistics
3 calculus
2 chemistry

I cant simply make find and replace because the records are a lot is there another way should I relate the tables of something how will it work?

View 8 Replies View Related

How To Combine The Values Of Two Tables

Jun 27, 2012

Here I have two tables. The first one categorizes items by certain descriptions from a fixed list. Each description, eg; "Big Size" is categorized in the table below with a minutes key. I want to create a table/query that is of the form of the 3rd table. I showed only the first record as an example, replacing minutes with each description.

Item
Size
Strength
Agility
A
Big
Strong
Slow

[Code]....

I want to "combine" these tables to make the table look like this without changing the design of the above two tables. If I MUST change the design of one of the tables, that's fine.

Item
Size
Strength
Agility

A
3
1
10

View 3 Replies View Related

Update Values In Multiple Tables

Oct 26, 2005

I am developing an access db where employees are allowed to load tools from a tool store.

I have three tables: tblEmployee, tblTool, tblOnLoan.

tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.

tblOnLoan is used to record which employee has what tools on loan.

When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.

When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.

i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.

Can anyone please help?

View 1 Replies View Related

Combo Box Values Being Stored In Tables As 1 And 2 Etc..

Feb 12, 2008

Hi,

I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...

View 2 Replies View Related

Auto Values In Access Tables?

Nov 7, 2006

I am writing an access database which has to store properties for an estate agent. They have a PropertyID field which is the primary key in this table. The ID is always in the following format PL219AD23 (postcode PL21 9AD and house number 23) Is there a way that once an address of a property is entered then the ID is generated from that? Or do they always have to enter the property ID themselfs.

Any help or tips would be appreciated..

Thanks

Fraser

View 4 Replies View Related

Insert Values Automactly In Tables

Nov 22, 2006

Hi, this is my first post in this forum... so thanks all for your answers...

Ok, the problem is this...

I've got a database with some tables, but in that database i've got 3 important tables....

Lets refer that the tables have this names [Table1,Table2,Table3];

In the Table 1, there are 3 important variables, one of them its a primary Key, ok all i wanna know how to do... its this:

When i insert a new regist in this table, the first 3 values from the first three colunns must be replicated to table2 and table3.

Can you help me PLEASE!! :(

View 10 Replies View Related

Query On Tables With Null Values

Nov 9, 2005

Hi,
I have a problem with one of my query, the query has 2 tables, the secondary field from Table2 is linked to the primary field of table1.
The primary field Doesn't allow null Value
The Secondary Field does allow null value which means that the Data in table 1 only concern Some of Table2's Data.
If I run the query with the two tables I can only see the records with the secondary field with no Null Value.
Is it possible to get the query Showing All the records of table1 2 and Table1 even if Table1 has no values?

View 1 Replies View Related







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