General :: How To Replicate Excel Index And Match Functions
Dec 19, 2013
I am trying to automate a data match I've been doing in Excel into Access.
Basically, I have 2 spreadsheets:
1. First has a "Start Date" for each user
2. Second spreadhsheet is a lookup table with 2 fields: Week Start Date and Week #. In other words, Dec 30 - Week 1, Jan 6 - Week 2, Jan 13-Week3, etc
For a given date in the first spreadsheet (e.g. Jan 10th), I am trying to figure out which week this falls into.
In Excel I use this formula where Column A stores the week start dates in the lookup table spreadhseet and Column B stores the Week#, with F4 being the "Start Date" in my first spreadsheet.
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.
name/ score/ peronal best) -> logic if participant has only one score - this remains his/her first best; if more than 1 scores - then the lowest gets 1st best, second lowest - 2nd best, etc....
I'm trying (without luck) to use some excel functions within an access query (namely NORMSDIST and NORMSINV). Error is being returned 'undefined function'. I have switched on the MS Excel 11.0 Library. Anybody any ideas? Thanks
I spent all day trying to figure out why sometimes I can call an excel function from access and the value will appear on my form, but for some functions (F.inv, binom.inv), it errors out as "object not supported". I browsed all the available libraries in tools/reference but found nothing that seemed to solve this issue.
I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.
If so, I have a conditional like this in excel: =IF(weekday(DATE-3)=0;DATE-4;DATE-3)
If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:
Private Sub PrepCustcmd_Click() 'Turn warnings off DoCmd.SetWarnings False DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit
Our Access database is getting a bit sluggish when loading/running reports. My mate said to me that the Access database that our 100+ users at work use to run reports should be "stripped back" to speed things up and to stop unnecessary actions running when they aren't needed. He pointed me in the direction of File>Options. Users don't edit data from this database, just to run preset reports. They have no editing capabilities either.
I've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017 ErrorDescription: Record is deleted. ErrorTable: tblFatura
ErrorCode: -1053 ErrorDescription: Index or primary key cannot contain a Null value. ErrorTable: tblFatura
ErrorCode: -1630 ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'. ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
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?
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.
I can use barcode scanning to track items through the process, on the condition that the relevant field on a form is highlighted.
The issue is I want the scanning/tracking with wireless scanners to be going on in the background while an operator can use the other functions of the database.
I'm thinking the solution is two computers, one to run the barcode scanning & tracking the other for the rest of the database functions. The problem is we only have one work PC and getting money for a new PC will be like pulling teeth.
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).
I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1 Class 0 3000 Class 1 2000 Class 2 300 Class 3 400 Class 4 500
there are certain txt boxes that once filled out, will filter a subform of a table of 1000's of records to give them a number to use on the form around 200 of the records have a depot in the "depot" field on the table, when they input a depot, it filters to them 200 fine if a depot is there,
what i want is, when they input the depot, if its there, filter it, if its not, to do nothing, as they could still get a unique number if the depot isnt in the list what happens now is, if the depot isnt in the list, it displays no records
Field : Fld_Depot Table : Tbl_Agreement_Summary Show : False Criteria : Like "*" & SearchForText([Forms]![Frm_New_Accounts]![Fld_Depot]) & "*" Or : Is Null
I've got an access form, and all I want to do, is to be able to type a number into a text box, click a button and then for the record containing that number to be displayed (in that same form).
-And I dunno where to start.
I don't like using the navigation bar, although that is almost what I'm after.Also, search functions I've seen on here that use combo box lists to display results seem like too much for me, as each number typed into my search box will be unique.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
I have a database that I have successfully replicated. It includes forms, queries and tables. Synchronizing works and all is good, but.....from what I keep reading on this forum, this is not the proper way to do it. It sounds like what I should be doing is splitting my database before replicating, and then just replicating the back-end.
When I do this, I find that when using the forms, etc. in the front end, I am unable to synchronize to the back end. I don't even have the option to do it through the menu options.
How do I make the front end recognize that the backend is replicated??
I have a command button opening a separate form from my main form page. After I enter in data in the main form, hit the command button and enter data in the second form. When I close this form I get the error message "you can't add or change a record because a related record is required in table "master table'. In my master table I have a primary key called "ID" set to autonumber. I believe the problem is this field should be replicated to the secondary table but it's not. How can I fix this?
I would like to be able to replicate the unique ID typed on form1 to all subsequent forms without having type it in. Is there a way to do this automatically? Any help gratefully accepted...JimT
This Access database is Web Compatible and is hosted on SharePoint. I have a form with a subform that display collections of records. As you can see in the attached image, the user selects a record from the combo box which filters the form/subform below. Each record will contain more than 1 item.
What I want to do is mimic a "Split Form". But, the last thing that is missing is being able to select an item from the subform (datasheet view) which displays several items in the record and have that item be displayed in the form. Currently, I have to use the record selector buttons at the bottom of the form to have the item I want displayed in full. But I want to be able to click the item in the subform and have it populate in the main form on this page.
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
