DSum, Specific Links Or Anything Else?
Feb 22, 2008
Dear all, imagine a table named "Database" with fields
Par1, Par2, ... ParN, Amount,
where most of the parameters in Par1,...ParN are in text format.
The structure of another table, "Criteria", is exactly like "Database" less "Amount" field.
Suppose we want to summarise "Amounts" from "Database" according to the sets of "Criteria", for each of its records. A set does not always include all of the fields, e.g. an empty field (or sort of wildcard) may mean that any value fits.
It seems like classical application of DSum function, but I've already spent a lot of time in desperate attempts to solve this problem and will highly appreciate your suggestions.
View Replies
ADVERTISEMENT
Oct 13, 2006
I searched and couldn't find anything on this specific issue.
I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.
The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.
Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.
View 1 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Mar 18, 2008
sorry if thi9s is the wrong place but I thought I would try somewhere.
I would like to create links to table trough an odbc link.
whta I have is an estimation programme that creates 4 tables in a directory/folder each time you create a new estimate I have an odbc link that points to the given folder where the tables are being created. What I would like to do is to search this folder and link specific tables to my database which is a contract control programme. the tabels are given unique name for each estimate example names are: 0001_assembly, 0001_data, 0001_increment, 0001_system
so to sum up is it possible to create a lnk to these tables on the fly. any one got any ideas and possible codes samples for doing this.
Thanks
View 10 Replies
View Related
Aug 22, 2006
Hi all,
I've got two vast tables of data which I need to link, however the field unique to each was, at it's source, a typed field, and as such both have errors, typos, formatting problems, known deviations etc.
An example would be something like this:
Table1: SFOC0912JB3
Table2: F0CO9I2JB3
(These are harware serial numbers for what it's worth). I could do with creating a link between the two tables which would return a true based on a number of possibilities, such as:
Match if:
- String matches with prepended 'S', 'C' and/or
- String matches with substituted 'I' and '1' in any or all positions and/or
- String matches with substituted 'O' and '0' and/or
etc.
I think Levenshtein had the right answer from what I've been reading, but I haven't yet found an implementation for access (freely) available.
Any ideas?
Thanks,
Alex
View 1 Replies
View Related
Feb 21, 2005
i have constructed a switchboard/main menu and have set up buttons which link to forms. i have also made a 'return to main menu' button which does as it says, the only problem is that after going back to the main menu both forms are now open...any ideas on how to stop this problem
View 4 Replies
View Related
Dec 20, 2005
Hi,
I would like to set up a form in Access that accepts the input of an Excel spreadsheet file (browse for file) and then creates a pivot table on one of the sheets, and appends that data to an Access database table..
can you give me some ideas on how i should approach the problem?
Many thanks :)
View 1 Replies
View Related
Mar 2, 2007
This may seem like a silly question, but I can’t find the answer to it on this forum. People only say 'Remove the table links' but never how. I can’t seem to be able to remove the table links completely, so I only have one database file (no backend or frontend). I can’t seem to do it though the Table link manager or any other way. Any help?
Thanks in advance
View 4 Replies
View Related
Feb 28, 2008
Sorry, cant find this in help, and cant do it by trial and error
How do I add a link to a post/thread in a reply
View 4 Replies
View Related
Aug 15, 2006
I have an existing database using access 2002 which has been split. In the part containing the data (back end I think its called) there are 3 existing tables which are clearly linked from the front end (forms queries etc.). If I now try to add another table in the back end it is not shown in the list when trying to create a link from the front end.
Anyone got the answer
View 3 Replies
View Related
Feb 28, 2008
i have 2 mdb and both mdb have table link
in one of the mdb , they had a link to dbf (paradox) and they have lot of queries and reports created.
Now the functionality has changed so we have created a access table and need to replace the dbf with the table.
The problem is we need to retain all the queries and reports as such but the table will be access table instead of dbf.
Example
First.mdb has test table
Second.mdb has a table called old.dbf. and associated quries
but now test table is been linked to second.mdb and we got to rename or delete old.dbf and associated all queries and reports pointing to test table
is there a way to do it automatically or should it be done manually?
Please let me know as it is urgent
View 1 Replies
View Related
Mar 5, 2008
I have recently learned how to link photos to a database using a form and a table listing the path to each photo. Is there anyway to format the records in the table so that when you click on the path the photo shows up?
I have browsed around past threads and have not found and answer.
Thanks
View 5 Replies
View Related
Nov 8, 2005
I working on a Project in which I have a table that is being updated from the values in another table. The problem that the table is deleted and rebuilt in code before the Query is executed. Access is so smart, it drops the links between the tables when the table is deleted even if the query isn't open.
To get around this, I recreate the SQL in Code for the Named Query involved just before I execute it. This works OK but probably creates bloat and is a little slower.
Is there a better way around this problem other than recreating the SQL for the Update Query each time?
Thanks for all meaningful suggestions. :cool:
View 3 Replies
View Related
Feb 20, 2008
I have a query where I am trying to set the where criteria expression using an unlinked table. The unlinked table name is INV DATES. The criteria field is [THE_DATE]. I cannot really link the fields for the two tables since I am using an expression. This design works, but it goes really slow.
Any ideas?
SELECT DISTINCT DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER, DSSAPP_FULL_WIP_PROD_STATS.OPERATION, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME, DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY AS QUANTITY, DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME AS [DATE], DSSAPP_FULL_WIP_PROD_STATS.LOT_HOLD_FLAG, DSSAPP_FULL_WIP_PROD_STATS.ACTIVE_FLAG, [INV DATES].THE_DATE, [INV DATES].WEEK_NUMBER_IN_YEAR, [INV DATES].MONTH_NUMBER_IN_YEAR INTO ROXES
FROM DSSAPP_FULL_WIP_PROD_STATS, [INV DATES]
WHERE (((DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R4*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "R5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "RR" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B0*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B1*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B2*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B5*" Or (DSSAPP_FULL_WIP_PROD_STATS.LOT_NUMBER) Like "B4*") AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_START_TIME)<=[INV DATES]![THE_DATE]) AND ((DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME)>[INV DATES]![THE_DATE] Or (DSSAPP_FULL_WIP_PROD_STATS.OPERATION_END_TIME) Is Null) AND ((DSSAPP_FULL_WIP_PROD_STATS.CURRENT_QUANTITY_TIME )>=Now()-600) AND ((DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="STPPRD" Or (DSSAPP_FULL_WIP_PROD_STATS.FACILITY)="EPIPRD"))
ORDER BY DSSAPP_FULL_WIP_PROD_STATS.OPERATION;
View 1 Replies
View Related
Jul 31, 2005
I wonder if you can help. I am trying to make a combo box with peoples ID numbers in which links to a table with that persons details. I have made the combo box with the names in but now trying to link the table to it.
For example: Fred is in the combo box with ID number 1 and the user selects him and wants to see his details about him after clicking continue. The form has autonumber 1 showing Fred's details after clcking this.
Any help
Cheers
John :cool:
View 2 Replies
View Related
Dec 2, 2004
Hi all.
As i'm still what can be called a "newbie" in MS Access, maybe one of you wizards
can help me out with this.
I'm having a product table in ms access, containing a few columns. The regular stuff (type, ID, ..).
It also contains a column i want to store the links to their documentation to, called "download'.
The fields of that table are loaded into a data access page.
Now the data access page is loaded into the frame of an HTML page. Everything works well, but when it
comes to the download-field, i get no link, but a piece of code instead.
Now these links are relative links (not absolute ones). What i'm searching for (for quiete some time now), is a way to make the links show as normal links.
Is there anyone who could give me a hint or who can help me out with this?
Thank you very much in advance,
Kind regards
View 8 Replies
View Related
Sep 23, 2005
Hi,
We've got an old database consisting of just an MDE file and its point to the UNC path of a server we need to decommission. Is there anyway I can amend the Links to an external mdb so it points to either the new server or folder?
Thanks.
View 3 Replies
View Related
Dec 14, 2007
Hello,
I have a splitted database. I have moved the location of these files to a new directory and when trying to run the refresh links, I get a warning for each table.
Is there a way I can avoid this?
I run the Linked Table manager utility but get always the window "select new location for table....". Considering that my program has a lot of tables, is there a way I can get around this problem? Thank you.
View 1 Replies
View Related
May 20, 2005
I was recently doing a little Spring Cleaning in my database and deleting old tables and queries that were no longer used or had been for experimental purposes. Unfortunately, I unknowingly deleted a query that was being used by a report I still needed and it took me several hours to find and correct the problem.
Not wanting to repeat this mistake, I began searching for a way to display my reports, queries, and tables and all the objects each is linked to. I thought it would be nice, for instance, to have a hard copy list of all my queries that showed where they are derived from and what other objects reference them. Is there a way to do this?
I've tried using the "Relationships" button and working with the Documenter, but I haven't found what I'm looking for. I have also tried a Google search, but, admittedly, I may not be using the proper terminology to get good results.
Thanks for your time.
View 4 Replies
View Related
Jul 5, 2005
I am starting to add a list of useful Microsoft Access sites and forums to help beginners to Microsoft Access find what they need. If you have any useful links, then either post them below or send them to me via PM.
I thought this would be a good place to ask as many people here know quite a few good sites.
Thanks
Aden
PC999 Webmaster
View 4 Replies
View Related
Jul 5, 2006
I am using 97 and have been running into problems with tutorials from microsoft since they are in 2000 format. Its not a big difference so maybe I just cant figure it out...
Anyways, I wanted to make a list of the reports I had for the user to click on one and have it pop up. Anyone know how I can make this list?
My idea is to somehow create the names in the list box using a query or something and have them be links to the report somehow...
Please help...Thanks :)
View 14 Replies
View Related
Jan 6, 2006
Hi,
I'm buliding an access system in which the forms and tables stored in seperate databases as such:
forms: \serverdirmyDatabase.mdb
tables: \serverdirdatamyTables.mdb
myDatabase has a load of linked tables from myTables. Problem; my network drives are mapped differently from the users, so the links do not work for me.
So - does anyone know how I can set up a relative link, so myDatabase looks for datamyTables? i.e. it looks from the source directory rather than checking the whole path back to the root?
Hope this is making sense . . .
Dave
View 2 Replies
View Related
Feb 13, 2006
I'm creating a leave (holiday) database but each year must be kept seperate for analysis. (I have split the front and backend of the database) I thought of using a dropdown on the main page which could be used to select the year which relates to the leave being dealt with. Depending on the choice selected (present financial year as default) the links would update to the required database.
Any ideas?
View 6 Replies
View Related
Mar 22, 2007
I am new to Access and just inherited a very large database to work with. Is there an easy way to figure out the relationship between each table and how they are linked? Thanks.
View 5 Replies
View Related
May 22, 2007
Once external tables have been linked to a database can the links ever get dropped accidentally.
ie should I test for missing links and recreate them programatically or assume that it will never happen?
It is easy enough to identify links which are present but any code which loops through the tabledefs will only identify links which are present.
I think the best approach, if this is necessary, would be to count the number of external links, compare the answer to what it should be, and refresh all the links if theree is a difference.
View 3 Replies
View Related
Aug 22, 2005
I have a form that tracks the budget. It is a small budget. But my problem deals with req. dates and req. amounts. You will see from the example I gave you that I can input one req. date and one req.amount and my calculations shows the ending balance. But how do I make fields where the same person can buy from different vendors in one day? I want to be able to show the date and the different requisitions and have the calculation subtract from each one, thus showing the ending balance. Hope this makes since. I attached the file of the example.
So under:
Date Req. I need to be able to put mulitple dates
Amount Spent: Different amounts that correspond to the date.
Ending balance: The ending balance will be correct when all of it is done.
View 1 Replies
View Related