Hi all. Having searched around I cannot seem to find what i'm after. I have a database which i hope to give users the option to run an append query which will copy certain pieces of information form one weeks to a another week.
Originally I thought about w/c + 7 which would give me a new week, but not all users were copying from one week to the next sometimes they would want to copy from one week to a date that was 2 or 3 weeks later.
What I can't find out is how to make the query append this date field to a date that is specified by the user.
Any ideas please so that I don't have to make some people angry with me.:(
I have a series of append queries that run when a database opens, to copy over the contents of various log files into a single table.
Each record has a field, [1-DateTime], which (surprisingly!) contains a date/time. When I open the linked text files from Access, these appear absolutely fine, all in US format (mm/dd/yyyy hh:nn:ss). But when I run the append query, it gets all confused, and puts them into my table in a conbination of formats!
Those that it can recognise as UK dates (e.g. 01/05/06) it stores as 1st May 2006, but then it gets to 01/22/06 and it decides that they must be US dates, so stores them (CORRECTLY) as 22nd January 2006.
hi Guys, I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
I´m trying to improve my appl. but I´m stuck trying to figure out how to append multiple rows on a table using the Append query.
My problem comes since I have 4 tables with the following fields: 1) Orders OrderID (number) CustomerID (number) Date (date) etc... 2) OrderDetails OrderDetailID (number) OrderID (number) ProductID (number) QuantitySold UnitPrice etc... 3) TmpOrders OrderTmpID (Autonumber) CustomerID (number) Date etc... 4) TmpOrderDetails OrderDetailTmpID (Autonumber) OrderID (number) ProductID QuantitySold etc...
The problem comes since the Tmp tables are used just to record temporarly the information before the transaction is completed. Whe the salesman at the desk finish the sale, a command button is presseed and an append query runs to transfer the data from Tmp tables to the Definitive tables. Another query (Delete) is excuuted inmediately after the append qry. and it deletes the information recorded on Tmp tables.
I need to reset the autonumber or create a field that records the line number so I can add up to the Maximum OrderDetailID found on the definitive table, but the problem is how to create the "controlled" autonumber.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible??? Ex. Joe March 3 to March 8 Mary March 4 to March 9 John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
I have a table which is connected to a form. I am able to see records in the form fields but i am not able to add anything in that table using form fields.
I have this survey application with the following tables - tblRespondent [PK RespondentID, other fields], tblAnswers [PK AnswersID, FK RespondentID, FK QuestionID, Answers {yes/no}, Notes {text}], tblQuestions [PK QuestionID, FK CategoryID, Question {text}], tblCategories [PK CategoryID, Categories {text}]. All ID fields are numbers, and all PKs are auto-numbers.
I also have a form based on a query that in turn is based on tblRespondent. That form is my main form, and I want to get the questions on it with the help of a subform, which is where my Append query comes into play.
When I enter a new respondent in my form, I put in some biographical information first, and then would like to send the newly-generated RespondentID to tblAnswers, together with QuestionsIDs from tblQuestions so that the Answers table receives a record for every question that I have.
I'm not having any luck with this, and am probably making some mistake somewhere. It would be great if somebody could point me in the right direction.
I was wondering if anyone had code for VBA to write an append query to append from one table to another, as i cannot seem to achieve what i want by using the wizard, any help is appreciated.
I have a database that contain foreclosure records. I'd like to create a query that will ask for a date and all records that are LESS than the date will be moved to a different table.
I'd also created an icon on my form and I'd like to attach this query to it.
I use a query daily that appends records to a table. I used it this morning but now it will not append records. It is odd because when I switch from design view to results my records are there but when I run it the records do not populate. I am not getting a pop-up message like usual either. It appears as if my access 2003 has disabled that function, which I can;t even find to turn back on. Any suggestions?? Thanks.
Please how can i use a procedure to create a query, then append the content of the query to a table (am using MS Access Project)? i did it in Microsoft acess database but now i need it on Microsoft access Project beacuase am transfering to SQL server. Thanks
Now i want to create an append query that will add in table ZALGA fealds PROIZ_I, TIP_I, DN_I, KOS_A, KOS_I by critera if TIP_A = TIP_I AND DN_A = DN_I then calculate KOS_Z = KOS_I - KOS_A?
I am building a database to hold information of training courses and staff that have requested or completed the training course.
I have built a form which the team leaders can use to request training for their team, basically when the form opens up it asks for team name and training session and then appends the names and training session ID to the main table. This then allows the team leader to tick the "request training" tick box which updates the table for each member they request trainig for. They would then send this using a custom command button which is linked through outlook.
The problem I am encountering is that if they were to selct the same team and training session again it would then append the same data to the table and this would create duplicate entries.
How can I set it up so it appends only once and then any other time the same data is selected by team leaders it would populate the form with the existing date rather than appending the same data.
