I have two tables. One is a simple two column table with Short Date and the corresponding Day of Week. My second table is an employee schedule based on Day of week. I bring the two together by the "Day" variable to generate a schedule for a specific time period, but I also want it to return the days where there is no data.
For example, I have a schedule listing like this:
J SMITH MON 8:00 16:00
J SMITH TUE 8:00 16:00
J SMITH WED 8:00 16:00
J SMITH THU 8:00 16:00
J SMITH FRI 8:00 16:00
My result is this:
J SMITH 9/18 8:00 16:00
J SMITH 9/19 8:00 16:00
J SMITH 9/20 8:00 16:00
J SMITH 9/21 8:00 16:00
J SMITH 9/22 8:00 16:00
I want to see the following:
J SMITH 9/18 8:00 16:00
J SMITH 9/19 8:00 16:00
J SMITH 9/20 8:00 16:00
J SMITH 9/21 8:00 16:00
J SMITH 9/22 8:00 16:00
J SMITH 9/23
J SMITH 9/23
Is there any way that i can see this without manually entering the "Days Off" in the main table?
I need a table that has all the year dates in a field (e.g. from 01 Jan 2008 to 31 Dec 2008) This means some 365 records! I hardly can type them all Wanted to know how to populate this table automatically.
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 am new to database and hope this is a simple problem. I have a few thousand products each with their own code and description.
I would like to create a form that when I type in the code the description self populates in an adjoining box. I would like to enter at least fifteen codes and their descriptions per form. Have tried related tables using forms and sub forms with child/parent link but any editing of code changes the information back on the table. Tried parameter query but how can I get more than one code on a form at a time? Any suggestions greatly appreciated.:confused:
I am looking to populate fields of one form based on another table with about 100 records.
Information Table Name: 1) StaticInfo 2) Data
Form Name: EntryForm (Based on the Data table)
I want to be able to select a value in a combo box in the EntryForm and have certain fields populated in that form based on the StaticInfo table.
Want to use all of the fields in StaticInfo to populate the same fields in Data. Historical data in the Data table needs to remain intact, meaning that if something is changed in the StaticInfo table, the historical records will remain unchanged in the Data table.
So, if I am able to select a department in a combo box in the EntryForm and have the fields populated in this form based on the StaticInfo table, will the records in the Data table change or not change if there are changes made to the fields in the StaticInfo table?
Also, could someone provide a method as to how I would go about populating these fields from table to form.
I created a form and in form I have created a combo box, and I can make as many selection I want through multiple box properties; however, its not populating in the table. Now if I keep select non in multiple menu I can input the value in table through that form. How can I make this field populated with more than one value.
Trying to populate a listbox using a .txt file created used WORD. This is the code i'm using:
Private Sub cmdDisplayFile_Click() On Error GoTo text_open_error Dim sTemp As String Open "m:myaccessumber.txt" For Input As #1 Do While Not EOF(1) MsgBox ("in loop") Line Input #1, sTemp lstShowTextFile.AddItem sTemp Loop Close #1
I'm getting nothing in the ListBox. Any hints would be much appreciated. text_open_exit: Exit Sub
I need to find out how to populate a blank form with the results of a query i.e. by selecting an item in a combo box and populating the form with the results.
:confused: I recently added a find button, using the wizard in access, i am searching three feilds ( Id, Last name & first name) when i type in these feilds and want other info to populate on the same form. now i need to update the code, so that when i hit the find button it does populate. can anyone help
Thanks
PS the form is based on a table and not a query, I saw some info in the forum about using a combo :confused: box, but i don't want to do that.
On my form i have a list which takes information from a table. I have set column headers to "on". What i want to do is populate my combo box with the column heads in my list box. Anyone have any ideas?
If var1(x) = "" Then var2(y) = "" If var1(x) = "1" Then var2(y) = "Set" If var1(x) = "2" Then var2(y) = "Act" If var1(x) = "3" Then var2(y) = "Prod"
' this is where I am having trouble- var 4(w) holds the name of the text box that I wish to use on my form ' and var2(y) holds the data I wish to place in the text box ' so how can I tell the code that the data in var4(w) ,290 for example, is actually a textbox name instead of ' just a numeric variable??
Hi everyone. I'm new to database design and hoping htis is a simple question.
I have nine thousand products each with their own code and description. I want a form that allows me to enter a product code and will then self populate an adjoining box with the corresponding description. I would like to enter at least fifteen codes per form.
I tried a linked form and sub form with a child/parent link but the tables info can be changed if the form user edits/deletes etc a code.
I tried a parameter query but don't know how to do more than one and the format isn't really suitable.
Any suggestions would be very much appreciated. Thanks :confused:Sunstar :confused:
I have an expression in a field on a report. I would like that calculation to populate my table. Do I ad something to the expression in that field that would tell it to take the sum of the field and place it in the table? What would the expression look like?
The report is created from the results of a data entry form.
Private Sub text458_AferUpdate() If Graduation_date = 2/1/2005 Then
TEXT_GRADUATION_DATE = "DATED AT THE CITY OF NEW YORK THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE."
End Sub
The Graduation_date is a combobox where the dates are selected. In a textbox titled "TEXT_GRADUATION_DATE", it should return "DATED AT THE CITY OF NEW YORK THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE."
Can a form be directly Bound to a dao recordset? I read an article form microsoft showing it being done in ado by setting the recordset property of a form.
I'm attempting to reverse engineer a Cascading Combo Box database into a Fire Extinguisher Audit app.The Cascading Combo Boxes.zip file is found at URL....I've successfully created the tables and forms, along with the appropriate Event Procedures. I've duplicated the one query, but there is no data.Do I have to populate the query with data? If so, why aren't there dropdowns in each cell like the original query? I'm not seeing any other table that contains all the data that the query has.
I wasn't sure where I should have posted this question so please feel free to move it if necessary.
I am not even sure this is possible and I didn't know where to begin but these forums have always been extremely helpful.
What I would like to do is export data directly from Access (via form, table or query) and populate it directly to an existing, external webpage.
A little background. My company orders cars an for certain manufacturers, Toyota for example, we order via a secure website. All of the other manufacturers have their order directly transmitted to them. The orders for Toyota take 3-4 times longer to place and I was hoping to speed the process by exporting existing order information directly to the webpage.
Please let me know if it seems like I am smoking something and I will gladly step away from the pipe....=)
