I'm struggling to get my database to auto split up a Query by a certain Column and automatically get it to export into separate Excel files.
I have got the export sorted, currently set to export the whole query, but i'm struggling to do the splitting up.
Hi
In my database i have a field called product code which the user enters the code as T0001, T0002, T0003 and so on for each record.
would it be possible when adding a new record for that field to auto fill the product code as T0004 for example, so the user doesnt have to type the code in all the time.
please help, ty
I am looking for a way of creating the following:
I want to be able to click a command button and have it prompt the user for input.
Then take the input and replace all cases of NA with that string in field B for all records.
I would prefer to have no other prompts other that the user input and a success msg box.
Can this be done?
I have a form that has numerous fields, there is one field that is called the "Relationship Code" it gives any customers who have a certain relationship a 3 digit number. I would like a command button that you can press that will take the current record shown and filter their relationship code, to only show those records who have the same code. I know this can be done by going to the field>right clicking>and saying Equals " ". But I want a button with code that will do this automatically.
View 1 Replies View RelatedHey people,
This should be any easy one to solve:
I need to split a field in my query into two separate fields, e.g fullname into a first and last name, so "John Smith" will appear in two columns, "John" and "Smith".
Thanks in advance.
I have a query with approx 700,000 results. The records in this query all need to be emailed.
I need to split the query results up in to separate queries containing 100,000 or so records so they can be uploaded to the mailing website.
I'm not sure if I need to make a new table for it, or I'm missing something.
I'm trying to separate LastName, FirstName into separate fields : FirstName and LastName. The code I have is working for the most part. But I had to create a separate query with the replace statement to get rid of the ","... Looking at the data now, I had people with names like Mac Buren, Tony and I'm only picking up the Mac part in my query.
Code:
SELECT Left([Employee Name],InStr([Employee Name]," ")) AS qryLastName, Right([Employee Name],Len([Employee Name])-InStrRev([Employee Name]," ")) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name]," "));
I am trying to split the year into thirds instead of the quarter. I will be needing to set the criteria to only show me the previous year from the current year. Can this be done in one query?
View 6 Replies View RelatedFirst query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
Getting ready to split a DB. No security really needed... Only the ability for multiple users needed. From what I have read here so far it seems best to use a MDE file on the front end and MDB file on the back end. One question is still not answered... I guess I will find out when I load the front ends on different stations.. BUT... I would like to know what to expect. I am assuming that each computer that I load the front end on I will have to go through and link the backend. Correct? I read a MS Knowledge base article about a form to do this... Is this only possible if you use the "developers edition" ??? Whats the common method for this task?
Thanks
Curtis
I have created an application that uses all the 'normal' factors of an access app. My forms are triggered by events that initiate some vba code which executes and then does something. No big whoop, we are all doing this; but I am going freakin' insane keeping up with changes. The users are using this app in a "live" test enviroment and the changes/updates are coming in quicker than I can type. "This field is not right it should read like this"
Well I can't change it until everyone gets out - they don't like this answer:D
I jumped in before thinking a few versions ahead and did not split the database :eek: I have read a lot of posts here and other sites and I can tell that I need to split this app but am a bit hesitant. From what I can gather I would have a
Front-End - houses all my queries and forms
Back-End - houses all my tables
I have a few modules, where would they need to go so that I can work on them independent of what my users are doing?
I would give each user a copy of the FE or make it available via network drive; would I then keep a seperate copy of my FE to make changes? If yes, does this mean my modules would be in the FE?
Can I split the db now that it has been in live production? What are some common errors that I should look for prior to?
I tried to split the db one time before, but my drop down list box(s) on the forms would not work. They are controlled by a query, not any code. Error msg stated could not find xyz sorry I don't recall the exact error
I need the ability to change, work on and update at will AND NOT effect my users.
I have this code from a Query that works.
SELECT Contacts.*,Contacts.Address1
FROM Contacts
WHERE (((Contacts.Address1) like "*" * (Enter 1st Line of Address here) & "*") AND ((Contacts.Site) = "Miscellaneous"))
ORDER BY Contacts.Site;
I want to replace the data "Miscellaneous" with a Public variable strSite.
If you can help I would be very happy and of course grateful.
Thanks
Ted :confused:
I have been asked to help with a database that someone is writing.
He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.
What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.
If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.
My question to the forum is the following:
With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?
I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.
Any and all help with this question really is appreciated.
Thank you
Bev
Hello,
I have been reading a lot about splitting databases on this forum. I still have some questions.
1) Will the FE (Front End) still show the tables?
2) Will users still be able to edit the forms, reports, etc.?
3) Will my code be hidden
4) Will all the users have up to date data showing when they open the Database?
5) Can more than one person open and input data in the database at the same time?
I also want to make an MDE copy, do I split first or make the MDE and then split?
The whole point is the following: I want the people (maximum 10) that will be using this database to only be able to do enter and view data. They should be able to generate the reports but not create new reports. I only want ONE person to be able to edit the forms, code, and reports. How would I do this.
Your help is much appreciated
I am trying to split my database but when i run the database splitter it comes up with the following error messages:
Subscript out of range
And
Invalid procedure call or arguement
Does anyone have any ideas what could be causing these or how to fix it?
Thanks
Hi
How can I change the location of the back end part, at the moment the front end is looking for the back end at a different location
Cheers
Bikeboardsurf
Hi,
I've split a database and the backend relationships are still intact but the front end they are not and it looks like this is causing a problem. Is this usual?
I've currently got a date of birth field in my database, but would like to query on just the birth month.
Can anyone tell me how to do it. Do I have to create another field which separates out the month, and if so, how do I do that.
Any help would be much appreciated.
Hi everybody,
I have been spending the last few days trying to find a way through the "access security Maze" which I believe I have just done. I have been following the "Ms Access Security Faq to the letter" and just now, I can open the database via a desktop shortcut including a custom workgroup address and I think everything is working fine. Now, I am trying to organise the next step which is splitting the application.
GHudson stated:
I find it easier to start out with one database that has everything in it
[including the security]. Then I copy the db. Rename them so that one is the
front end, one is the back end. The security will still be in both since they are
exact copies. Then remove the shared tables from the front end, remove
everything else from the back end [except the tables that will be shared].
Then relink the shared tables from the front end to the back end.
Using custom shortcuts will make it easier for the users to open the front
end with the correct workgroup. You can customize the shortcuts target
field with all the required file and workgroup info.
I Understand prtty well what's being said but am getting a bit confused of where and how the different part of the application should be organised on the network:
*The back end file should be on the network drive(drive F)
-That's fine...
*The front end should be distributed only on the station using the application:
1) Does that mean that I could place the FE on the F drive for a short
while and ask the different user to copy the application and past it on their own drive and then delete the FE of the share drive.
2) I will have to use the custom shortcut with the specific work group on each station. Should they all have the same shortcut which would mean that there is only one FE on the F drive and not make much sense or Should the shortcut link to the FE application save on their drive which doesn't make much sense either because they could open the DataBase without the shortcut with their own "default" Workgroup and wouldn't be secure anymore.
I apologize for my confusion and have probably missed a basic step in my reasonning but if anybody could redirect me in the right direction, I would really be gratefull.
I setup my security (or lack there of, hehe) and split the database. I relinked the tables.
Now, Access gets hung up when I try to create an MDE of the FE to distribute. It starts the process, asking me to select a folder and file name, I do that, and it gets no further in the process. Access says (not responding).
Should I have created the MDE before I split it? Or does crashing of Access have nothing to do with whether i create the MDE before or after splitting?
Hi, hope someone here has experience with this issue.
Our system was recently converted to much tighter secured network environment and moved to Office 2003 (still Windows XP).
Within the next couple of weeks we expect to split/secure our new db for shared use across a network (approx. 6-8 users at any one time).
I want to keep the db in 2000 as long as possible because I'm doing lots of the work at home and that's what I have. After deployment there will be lots more changes but in managed releases. (sounds official, eh?...)
Does anyone know of any risks I'm running by keeping it in 2000 within Access2003?
Thanks very much,
Hi,
I have been searching for more info on this and found out quite a lot about it; however, I am still wondering how to connect the front ends that will be in different machines to the back end that resides on server.
I know that when you split a database, the wizard asks where to save the first front end. But how about if you have many?
Any help will be very much appreciated.
B
Can anyone help me to split a number using an access query to the left and right of the decimal point. I found it in a reference somewhere but have lost it. the number is generated in a query and I need to do something with the fraction
Thanks
I have 2 questions.
The first is regarding splitting a database into 2 files, one for tables, and one for forms and queries. I have a database whose data will be accessed from about 4 computers, and almost never will this data be accessed concurrently. In that kind of situation, is it easier to just put the whole DB in a share and give each user a shortcut to it, or should I be splitting it and just putting the tables on the share?
Second question:
The option in Access 2007 to make the database an .accde file. From what I understand this just locks down the structure of the database so it can't be changed. Is this correct? Can it be edited by an admin once it's converted to this format, or does it just stay that way? I guess if it can't be edited, one could just keep a copy of the .mdb, modify that, then reconvert it to .accde.
Also, if I were to split my DB, would I first split and then convert to ACCDE, or the opposite?
Can someone enlighten me on these few things?
Thanks
i thank all for helping with the issue of mulitple users. after i did the splitthe database using the wizard. i noticed the tables lookes diiferent. but how do i put the front ends for users in their document since am on a thin client enviromemt. or
how do i transfer the front from the end. do i have to write codes??
thanks
Hi. I would like to split my database which is used by various people on a shared drive.
Is it possible to specify a relative path to the linked tables in the back end?
Otherwise, if people access the shared drive via different drive letters, it fails.
Also, I like to keep a copy of the database myself. With absolute paths to the back end tables, I can't simply copy both sections from one place to another - as the links fail.
Thanks.