Is there a way to make a query that has several steps that have to be run in sequence, rather than all at the same time?
For example, I want to create a table that is created from 3 other tables that I download.
The first step would be to append all titles from the first downloaded table, not allowing duplicates.
The second step would be to append all titles from the second downloaded table, not allowing duplicates.
And the same with the third step.
It would be nice to be able to do this with one query, but since it gives me an error because I am trying to do the same thing three times to the same field, I can't. I would really like to avoid having to save this as three different queries, like I've had to do with other multi-step queries.
If there's a way to do this, I'll be able to apply it to several other multi-step queries I have.
Also, if that is possible, is it also possible to have several types of queries in one? Like an append query with several update queries, performed in sequence?
(Note: I'm creating these queries in Design View--I don't know enough to write my own queries in sql, except for my one union query--which I had help with--from here, I think. I can, and often do, alter sql queries that others have written for my own purposes, though.)
I have a table wherein only new data is provided by the user, and fields that have not changed from the previous record are left Null: Date Field1 Field2 1 B (Null) 2 A 1 3 (Null) 2
Is there a simple way to produce the following results from an aggregate query: Date Field1 Field2 3 A 2
(Where [Date] is a parameter.)
Right now my process is pretty clumsy, requiring multiple steps to get the MaxOfDate for each Field, then each Field's value corresponding to its MaxOfDate. Not pretty. :(
I am working with several reports between Access and Excel and I feel like there must be an easier way to do what I need. Hoping someone can help.
I have 4 Excel files, A.xls and B.xls and C.xls. Each with different fields that I will use to join and extract data to make D.xls.
A is the master file that I need to compare B and C to. I need to create a 4th file that will contain all the records on A and only those on B & C that match A. I am using email and ID as the join fields. A has email, B has email and ID, C has ID. I am joining A & B by email to make query A_B which contains all A records and only those on B that have same email. A_B now has fields email and ID. Then I need to use query A_B to join to C. The problem here is that I cannot use the query A_B to do this. I get an error message and have to export the query to Excel then bring it back into Access as another table to use for the last join. This is where I feel like I am doing an extra step. Is there a way to use this last query for the next join so I can end up with a final file that has all A records that contain B data and C data?
I know this sounds confusing but I hope someone can understand and provide guidance. Thanks!
MS Office 2003..I have 7 queries that I use to export data to one excel workbook. Inside the workbook are 7 worksheets, one for each query. I have it working but I dont know how to save the exports steps like I did in AC2010.
I export the data by right clicking on each query and select export , then I browse to the workbook and because each query is named differently , it names the worksheet. I have to run these 7 queries each month , so I would like to save the steps and then build a macro that would run all seven steps at the push of a button.
I have saved the steps on a PC running MS office 2010 and the macro works great. The problem is this resides on a PC that cant be upgraded and I need to make it work on Access 2003 .
I'm enrolling medical centers in a new multi-center database. There are a number of steps that must be completed before the center is enrolled in the project. I have a form that serves as a checklist. I want the textbox at the bottom of the form to display the percentage of steps completed. I guess you would have to count the number of fields on the form that are filled in, but I don't know how to do it.
I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?
I have duplicate loan numbers in a table. I want to create a query that will assign a number so the the duplicates will have a new field called sequence. example
I have encountered some issues with the dates. For e.g, Jan-06, Feb-06, April-06, May-06 Is there any way to determine that Mar-06 is missing from the list. The listing will be growing.
Cause I need to have a culmulative counter so Jan-06 to Feb-06 will be counted as 2. But if there is a break in between, in this case, Mar-06, the counter will be set to 0, so for Apr-06, the counter will be 1.
Have been troubled by this issue for a long time, I appreciate all help given, thanks!
Hi... i have a excel file..... from which i have imported records.... after importing.... the records sequence in my table has changed..... can anyone tell me how to preserve the sequence that i had in excel..........
Not sure whether I am in the right forum. It is Table, Form and Query.
Have a table which index to sort on 4 fields - Proj, SubProj, Team, SubTeam.
1. I used this Table on a form, the data seems to be in random sequence instead of above.
2. Then I tried to sort it in a query which showed up fine ( as required) in the actual running of the query. However when I used this query in a form, the data doesn't sort as expected.
3. With above 1 and 2, I have also tried the Order Of in the Form properties which still do not do the trick.
can anyone shed somelight on this please.
BTW, After I first created the form, I just rename the record source in form property. Don't know whether this make any differences in the above result.
I tried this question a few weeks ago, but I'm trying again.
I have 2 Access 2002 files. One has a sort order of ascii and one has a sort order of international. I need them to both have the same sort order. It doesn't matter which one. Right now I get the above error message on one of them.
How can I change the sort order so that I can import Paradox files like I used to? :eek:
Hi, Its good to be back after a long time. Hope everyones fine. I have a query and would appreciate any help.
I have a master-table (tblM) and two related tables (tbl1, tbl2) with one-to-one relation.
Relation1 : tblM.ID (autonumber) related to tbl1.caseID (number;LongInteger) Relation2 : tblM.ID (autonumber) related to tbl2.caseID (number;LongInteger)
Relations are cascaded (referential integrity imposed).
I import data from excel and feed fields of tblM (I dont feed ID field from excel as it generates autonumber). At a time I feed 150 records (daily).
I have a front-end based on query from three underlying tables. Data-entry-operators do not work on tblM data as it is readonly in the front-end. They enter data in the tbl1 and tbl2 columns.
I noticed as they start entering data in tbl1 columns, the tbl1.caseID column gets data from tblM.ID on its own. Same happens in the case of tbl2.
My observation is : I find that tblM.ID is not equal to the number of records available in tblM. (ie tblM.ID is more/less than number of records in the table. So the tblM.ID generated is not in serial.
Next time as I start importing data from excel file to tblM it gives me error and not allowing further import.
I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....
I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.
I'm running an update query and I get this error - Function Sequence Error. (#0)
I have an excel spreadsheet that I imported into a table. I am trying to update the part table with date from the spreadsheet. The part id is unique and thats how the tables are joined. I only need to update 5 fields. The table I'm trying to update is a linked table.
I can't figure out what I need to do to make the necessary updates.
I work for a non profit agency, and we offer a Representative Payee service (paying bills for those who can't handle their own money). I've created a database that keeps track of everyone's finances, and gives us all the reports we need. The only thing I have not been able to do is to actually print the checks.
I have a report that pulls up the data (in check format ... including making $1.00 say one dollar and 00/100). But I'm stuck trying to give the checks a check number. I can't figure out how to pick a number to start with, and then add numbers sequentially.
I want to create a custom sequence when deleting a record but I'm having a problem.
What I want to do is prompt the user for the reason he/she is deleting the record and then archive this so it can be viewed in a log at a later time. This extra step is not as easy as I thought it would be.
What I did was put some coding into the On Delete event of the form. The code opens a form with a text box. The user can enter the reason for the delete in the text box and click OK or click cancel to abort the deletion.
My problem here is opening another form from the On Delete event. As soon as the form is open, the code just passes to the next step of the sequence and deletes the record.
There are 2 approaches that I've thought of but I'm not happy with either.
Approach 1: In the On Delete event, set Cancel to True. That way the deletion is not handled by the delete sequence but rather by the new form that opens. The problem is that deleting a record from another form is not that simple. What I've tried is to execute a SQL statement that grabs the ID of the record from the open form and delete it. The problem is that although the record is deleted, all the fields on the form say #Deleted in them which is not that pretty. (Maybe there's some other way to do the deletion without this problem that I'm not aware of...?)
Approach 2: Use an Inputbox. Unlike a custom form, the Inputbox delays execution of the code so the code can determine from the results (i.e. should it cancel the delete if the user clicked Cancel). The problem with Inputboxes is that they are not very flexible in the layout, they look pretty "packaged" (i.e. they look to me like the programmer didn't bother creating a means to enter a value). There's no way to place the buttons where you want or have it do better validation (e.g. I want the FORM to check that there's at least a few words describing why the user is deleting the invoice rather than have the form with the record get the input message passed back and then give an error and then pop open the Inputbox again)
I think that especially when you're creating an application with anything financial, you'll want to log a deleted invoice including the date, amount, possibly the client's name, invoice number, reason for deletion and the login name of the person doing the deletion. All of these are easy to record other than the reason.
1. If accidently a record or two in a sequence are deleted, how will you know that this has happened! What type of validation can be enforcedto prevent this?
2. How do you make your Tables, Quries, Forms, Reports, Macro tamper proof?
I need to create a button that generates a sequential four digit requisition number and places that number in a field along with some text. I cannot use autonumber because this number is not and cannot be the primary key. In addition, I need to be able to reset the number to 0001 at the beginning of each federal fiscal year. Here are the details:
reqtype: An option group that has these choices: PC, TL, and RQ actionyear: A drop down box that lists the current fiscal year: 2007 reqnumber: A field that, when the button is clicked, combines a text prefix, reqtype, and actionyear with a four digit sequential number.
The end result should look like this: SEC-07-PC0010 and be recorded in the reqnumber field.
My code writing abilities are limited. Could someone please help me out. Thanks.
I have 2 single-page Access reports. The first report has the following fields: FirstName, LastName, StreetName, HomeNumber. The second report has the following fields: MerchantA, MerchantB, MerchantC.
The data for the first report are taken from tblBuyers, the data for the second report are taken from tblMerchandise.
I need to print 2 copies of the first report and 4 copies of the second report for the first Buyer, then 2 copies of the first report and 4 copies of the second report for the second Buyer and so on. Can I help on this problem ? Rgds
Hello, I have no idea what to search for to see if someone else has already asked this question. I've tried several things with no luck. So here goes...
I work in a library. We deal with thousands of journal subscriptions--we cancel our subscriptions and order new journals all of the time. Every paper journal we have is assigned a unique number, called a ZP number. When we throw out a journal, that ZP number can be assigned to a new journal.
I would like to find the earliest missing number in the sequence without having to check manually. Is there a way I can do a query or something to find missing numbers in the sequence? The ZP numbers start with 1 and go up to 9999.
So, I have two fields: Title and ZP#.
How can I do this?
This is a cloned table, used solely for assigning ZP#'s, so it can be altered in any way--including adding records with blank titles for each missing number.
I have to produce a report (preview first)that acts like a cross tab query. By this is mean that it reports specific data in a 12 period sequence for each year. Therefore I have to set up dates which are NOT normal calendar dates ie one could be 02/05/05 to 29/05/05 then the next would be 30/06/05 to 24/07/05
These dates are issues at the start of a contract year. I have to therefore produce the report for each period that is given to me ( not an easy one is it) I have considered many options but I cant quite get the final effect
I've searched the forum and only found one thread which covers this but doesn't help me.
I have a table with peoples individual information in it.
Every person has a unique ID number.
I want to find the missing ID numbers when I delete a person so I can keep the numbers in sequence.
I am not using autonumber. The reason for this is coloured wristbands. Each wristband has a unique number and they are broken into 4 colours. each colour has a number range 1 - 300, 301 - 600, 601 - 900, 901 - 1200. Each person in the table has to have an ID number matching a wristband. The people are also broken into 4 colour categories. This means that not every range is filled before i need to start using the next range.
