Filemaker Lookup Equivalent
Nov 19, 2007
Hi all,
Sorry for such a Newbie question but....
I'm trying to find the equivalent of Filemaker's Lookup function within Access. To quote the Filemaker help page:
"A lookup copies data from another table into a field in the current table. After data is copied, it becomes part of the current table (and remains in the table from which it was copied). Data copied to the current table doesn't change automatically when the data in the other table changes.
To establish a connection between tables for a lookup, you create a relationship. Then you define a lookup to copy data from a field in the related table into a field in the current table."
The requirement for this is for an invoicing database where we need to copy a customer's invoice and shipping address into the invoice record so that, if the customer changes address, the old orders retain the actual address that was shipped to rather than be updated to whatever the customer's current address is. The same applies to many other things that fluctuate over time such as promotion discounts and list prices. When these change, looking back through old orders will give different prices than were actually invoiced. This must be a very common situation.
I've searched high and low on the web for an answer but so far have come up with nothing. A workaround might be to create the relationship then, when the customer ID (or product code etc) is changed, fire off a macro that copies the data from the related fields in the second table into the appropriate fields within the current table. If the Filemaker Lookup functionality is already present within Access it would be much neater to use that instead.
Any ideas?
Many thanks,
- Andy
View Replies
ADVERTISEMENT
May 3, 2006
I have noticed this site contains a google add to filemaker pro.
Is filemaker pro any good. I am making a fairly complicated db, with upto 15 concurrent users. I am using SQL server as the BE.
Would filemaker pro be a viable alternative or is it just a easy way of making a simple database.
Ta
View 6 Replies
View Related
Oct 13, 2005
Hi, im an access user, but i keep hearing good things about filemaker pro. A friend of mine is useing it for a cutomercontact database, and hes really optimistic about it.
Has any got any experience with it. Which is better? Access or file maker? Why?
thanks for all the reply's :D
View 1 Replies
View Related
Jan 22, 2006
I have been asked to recreate an existing database which was done in FileMaker into Access.
Rather than re-enter all the data which is in there, can I just import it into Access?
I tried exporting the FileMaker data into Lotus 123 format and then inporting it to Access that way. Filemaker has a bad habit of not exporting duplicate entries. I need this data as well.
Any suggestions would be greatly appreciated.
View 1 Replies
View Related
Mar 21, 2007
Just a quick (hopefully easy) question.
Does anyone know if it is possible to link to Filemaker Pro tables for Mac, into Access?
The Filemaker DB will hold the data, with Access displaying the forms, along with some additional tables.
Does anyone have any ideas?
View 2 Replies
View Related
May 3, 2007
hi,
i justs start experimenting with database applications and i think filemaker is a dam good one. i find it very easy to collaborate my MS Excel knowledge with in it.
my qustion is can I use formulas such as this below in access to automatically calculate a fields based on another fields.
If(shift start time>shift end time;(Int(2400/100)+((2400-Int(2400/100)*100)/60))-(Int(shift start time/100)+((shift start time-Int(shift start time/100)*100)/60));If(shift start time<shift end time;(Int(shift end time/100)+((shift end time-Int(shift end time/100)*100)/60))-(Int(shift start time/100)+((shift start time-Int(shift start time/100)*100)/60)); and so on
View 3 Replies
View Related
May 20, 2005
Hi Folks!
Need help! Is there a BCP equivalent in access. I want to load 2 million rows into an access database.
Any pointers will be of great help.
Thanks,
Moksha
View 1 Replies
View Related
Jun 7, 2005
What is equivalent for Last function in access to that in SQLserver
View 1 Replies
View Related
Oct 12, 2005
Hi,
I have two access tables :
Table 1 : Customer number / E-mail
Table 2 : Customer number / Address / Phone
I like to add the data of table 1 to table 2 :
Customer number/ Address /Phone / E-mail
When of course the customer number is matching in both tables .
Can someone help me ?
Thanks
View 9 Replies
View Related
Sep 27, 2005
I need to use dlookup in a query to populate a new field "number" with values obtained using a current field "letter". I have a (lookup) table as follows: Col1 = A, B, C, D and Col2 = 1, 2, 3, 4 which of course gives the number code corresponding to each letter.
In Excel, I simply use the formula: vlookup("letter", "lookuptableref", 2, false) and it populates my new field number with the correct values.
Can anyone help me with the equivalent in Access using dlookup?
Thanks!
View 3 Replies
View Related
Jan 16, 2007
this is vexing me.
I have 4 tables, 2 entities, 1 relationship and 1 lookup
Entities are tblOrg and tblForm
Relationship is tblVentureParticipation
Lookup is tblInfo (Has many different lookups)
When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form
I want to identify the forms that have NOT been turned in.
So I figured
Step 1:
Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)
Step 2:
Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')
Step 3:
Remove all of the entries in tblForm that match the result of Step 2.
I have verified that both of these individual queries return the right information ...
View 1 Replies
View Related
Jan 10, 2008
Hello all,
I am trying to improve the network performance of my database and I need to convert the DCOUNT function into a SQL statement.
my code
=DCount("[Queue]","qryODFQueueNBET")
The tables are stored in the backend.
I have about 30 of the DCOUNT functions and it is really slow when running in the network.
Thank you,
View 1 Replies
View Related
Oct 16, 2013
I have got a combo box with a product code in it, and depending on which code is selected I want the data to come from one of two different tables. Is there any way I can do something like a match formula in Excel to see if the stock code is contained in a table?
View 3 Replies
View Related
May 26, 2005
I am an intermediate level Access user and I am trying to have one of my tables lookup data in another table and populate the corresponding data. For example, TABLE 1 and TABLE 2 both have a common field, MATERIAL field. TABLE 1 contains a SERIAL CODE field which is all the serial codes and TABLE 2 does not. I am trying to copy over the serial codes for 1000s of lines of data from TABLE 1 to TABLE 2 via the corresponding MATERIAL field. How exactly can I do this? Please be specific and do not leave any steps out if I need to enter functions, etc. Please tell me exactly where the process needs to be implemented.
Thank you very much in advance.
Ease
View 4 Replies
View Related
Dec 6, 2013
i'm using a sql procedure which i'd like to implement the equivalent in ms access. i want to know if they can be any possibilities. this is my procedure
Code:
CREATE PROCEDURE CreateOrders
(@cartId char(36))
As
DECLARE @CmdID int
INSERT INTO Commande DEFAULT VALUES
SET @CmdID = @@IDENTITY
INSERT INTO DetailsCommande ( CmdID, ProduitID, ProduitTitre, Quantite, CoutUnitaire )
[code]...
View 11 Replies
View Related
Sep 23, 2014
How can I convert a date format to it's numerical equivalent inside of a query? For instance, 1/1/2014 to be 41640.
View 2 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)
Value1(Double)
Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 3 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)Value1(Double)Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 11 Replies
View Related
Sep 30, 2012
PRODUCT
VALUE A
VALUE B
WW
10
100
XX
20
200
YY
30
300
ZZ
40
400
I have above table in access. I want to use this table in query to find value of any product using a formula (Value A*X)+Value B. The product and X query picks up from another table. The same can be done easily in Excel using Index, Match formula but how in Access.
View 1 Replies
View Related
Mar 10, 2014
Whats the best way to show the text result of a combo box selection when displaying the information through a query? I notice the table is also reflecting only the numerical ID result of the selection so I'm not sure how to take this result and display it as a text result to the user.
View 7 Replies
View Related
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
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
Mar 2, 2005
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
View 1 Replies
View Related
Dec 9, 2005
Hi there,
In Excel, I use Application.EnableCancelKey to stop a user from interrupting a macro whilst it's running. Is there an equivalent piece of code in Access so that it stops a user from using ESC or CTRL+BREAK to stop/interrupt a code from running?
Or can someone suggest an alternative way of achieving this?
I have some code which runs a query and then exports the data to an Excel file and I do not want a user to interrupt or cancel the routine.
Rgds,
Paul.
View 1 Replies
View Related
Nov 3, 2005
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2]
FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following:
[table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as:
[table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I appreciate your help in advance.
View 1 Replies
View Related
Jun 19, 2007
In .NET, there is a function called .LastIndexOf but what would that function be in MS-Access?
Thanks...
View 8 Replies
View Related