That subject line probably doesn't really explain it !
I am doing some support for a database that has group level security (I can do the support and make the changes they want but I'm not so hot on the security side of it !:o Anyway when I'm in the office that's fine, but when I want to work offline at home I can't open the database because I'm not logging into the server obviously and it seems to be looking for that.
I have the database. I also have the mdw file with the user and groups permissions etc.. How do I separate the database away from this so that I can open it? or how do I open it? I have username and password for admin but I don't think that's any use to me? cos I don't really know how to log on off site...So if there is anything I can do can you please let me know in simple step by step instructions !:confused: :confused:
I have a single field which contains 13 months of budget data separated by ;. See the example below. I'd like to separate the data into 13 individual fields. What function would I use to do that? Thanks in advance.
I have gotten to a point with a database where I running into some conflicts when 2 or more users are running operations (when run an append query the second user will get the first info and the first user won't get anything) and I'm thinking it is time to make the database clientserver. Is there anything I should look out for when making this change or is it time to rewrite the whole program in VB? What is the easiest way to do this? I have about 10 users and the database is about 25mb.
I need to eventually create a YTD report with the following information, sorted by customer number and invoice number.
Here's my situation: If a customer is a current customer, their income amounts show up in the current customer column, and the commission on that amount is calculated based on all current customer income amounts added together, and then compared to a sliding scale. I have this working. If a new customer's income (for all their combined invoices) is <=200,000, I need to list the income for each of those invoices in a separate column for new customers. Once that customer's total income reaches 200,000, I need to display the income in new customer column up to that point, then any income over 200000 needs to be displayed in the current customer column. So if the amount 200,000 is reached in the middle of an invoice, part of it needs to be displayed in the new column, and part in the current. Also, the amount over 200,000 now needs to be added to the grand sum of all current customer income. I show a sample table below - any help would be greatly appreciated.
Every week by e-mail from a supplier one I get an excel file with 7000 or so data entries (special offers).
I import this into Access and then merge it with Word to produce a printed catalogue. A lot of this data duplicates week from week and the manual job of sorting through and deleting out previous entries all 7000 entries is getting too much (and is not reliable - I'm only human after all)!!
Is there a way (probably with a query) that I could automatically compare the latest table with the previous week' s table and produce a new table that only contains the changes (i.e. only the new data that has been added since the previous week).
This would make my job so much easier!!!
A primary key (?) could be the bar code or unique product reference.
I have a quick question. I have a field that has information that looks like the below. I need it to be in 2 separate columns, but there is no space between the numbers and text otherwise I would know how to do this in excel. Anyone know how to do this is excel or access?
I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:
The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.
I have strings of names in a calculated field named [CurrentDelReverse] that are separated by spaces, but some strings have 2 names and some strings have 7 names.
My problem is trying to grab the second name in the list to show in a new column.
I have tried so many different things and the closest I got to solving it was: Delegator: Trim(Mid([CurrentDelReverse],InStr(1,[CurrentDelReverse]," ")+1,IIf(InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")=0,0,InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")-InStr(1,[CurrentDelReverse]," "))))
This returned the second name in the list but ONLY if it had more than two names in the string.
I have a textbox on a form that i am pulling off of a table. the table stores the infor as listed below
customer ordernumber order description
Jeff 123 -abc -def -ghi Bill 456 -ghy -hig
but when i bring the field into a form it lists it out the field of order description as below in a textbox, i used textbox b/c the the text can be pretty long
-abc-def-ghi
how can i make it so that it separates out the listing as i have it on the table?
Code: Select DPTag from tbleqDP UNION ALL select ESGTag from tbleqESG
It is returning all the tags from both tables but putting them all under "DPTag." What I want is two columns in a report. One would have the header "DPTag" and all the values under it would be the tags from tbleqDP and the other would have the header "ESGTag" and all the values under it would be from the table tbleqESG.
What am I doing wrong such that it is returning all the values under the header DPTag?
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code: TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID] SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID] FROM Register GROUP BY Register.National PIVOT Register.P_Gender;
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
Well, I am facing one problem..in my application; I need to show all forms / reports name of other database( .mdb ) file without opening the other database physically. I tried a lot but didnt succeded. I tried with below code..
Set AcApl = New Access.Application Call AcApl.OpenCurrentDatabase(strfolder, True) Set AcProj = AcApl.CurrentProject
Set frm1 = AcProj.AllForms
intCount = frm1.Count
But here wen the second line AcApl.opencurrentdatabase get executed at that time the database get open physically, and i dont want that..So is there any other way around..If so..please please help me..
i have a database that runs updates from within itself.what i need is, this database to then open a another database run a update query, then close it.
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
I have a problem that seems to be happening on several users' databases and is causing a big problem. None of the databases is a shared database...they are all single-user databases on stand-alone computers. I have tried looking for help within previous posts, but all seem to be related to shared databases.
I am getting an error message: "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." The database cannot be opened, imported, repaired...nothing seems to work.
Again...these are NOT shared databases. I appreciate any help I can get. I created the database for all of the secretaries in our school district to keep up with absence data. It involves many tables, queries, forms and reports, and has generally worked well. However I am now seeing several that are getting similar errors as mentioned.
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
Is it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
I created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open. and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.
I am working on a database that will be an addition to an existing one on the company server. However, to make the overall layout not so complex and allow room for other additions in the future, I'd like to keep the databases separate. This will also ensure more efficiency, integrity and troubleshooting overall.
