Saving Daily Generated Query For Later Lookup By Date?
Feb 26, 2014
I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.
I have an example of the query currently I have set up in access 2007:
School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014
VS
School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014
I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
I'm trying to make a form where the final user can type in two dates (date range) and the result will generate a report on all activity between those two dates, any ideas ?
I have created a form taking in Data from a query.On the form I have created a combo box. The selected value is used in an criteria of the query.The criteria in the query is:[Forms].[frmMyPortfolio]![cbodep].How can I ensure that all values in query are generated, if their is no selection in the combos.
Good Evening Everybody, I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:
ID: Auto Number Area: Text Equipment:Text EquipSrlNo:Text EquipLocalNo:Text MOPNo:Number DateDone:Date/Time MOPPeriodicity:Text
I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???
SQL SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity FROM [Main Data] WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));
Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.
Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:
Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0) Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0) and so on for 31 fields.
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice 01/01/2015 101.45 02/01/2015 104.70 03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
I have a scheduling database that requires keeping track of events on a 24 hour basis. I need to keep track of all the events through out the day by date and time. I want the user to only have to enter the time instead of entering a date and time. What is the best way to allow the user to enter only a time, but when the time is saved to the database, it’s saved as a date and time using the current date? I’ve tried using the date add function but that doesn’t work. Does anybody have any ideas.
Embarrasing question, but my serches have proved fruitless.
Saving a Date field (Short Date) is saving as 1/02/2008 or 27/02/2008 in the table. Formatting this data in a query as dd/mm/yyyy displays correctly, but the mid function mid([Date],4,2) still picks up the table format, not the query format
My objective is to extract the 02 (month) using the mid function in a query. Beacuase the length of the day component varies from 1 to 2 characters, this is proving impossible
Is there a way to save the date as 02/02/2008 in the table?
Tried using input mask dd/mm/yyyy but kept getting invalid format error
How to build query to give daily balance across bank accounts? (to then plot in a graph)
Assumptions:
* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX. * Table BANKS which has BANK_ID and TITLE
Would like a query that gives: Supply StartDate and EndDate for the query:
Hi, I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure) Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.
Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.
I'd like to have a button on my dashboard that gives the user the ability to back up the database and save it with a different date. For example, existing database called "Fishing.accdb" and I'd like to have code behind a button that allows the database to be saved as "Fishing0711.accdb"
I'm trying to avoid the user having to go into the File Save as menu system....I've tried the docmd.copyObject method but can't get it to work
I have a date field that is just a typical date field. When I do a query, I want to look up by just the YEAR in that date field. Can I do that or do i need a separate text field with JUST the year entered in? or can i have a field in my table that just looks up the main date field and displays/uses ONLY the year??
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields ContractName IssueDate SubSup DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
Quote by Pat Hartman >>>BTW, one somewhat undesirable thing happens when you go with replication. Access changes ALL your autonumbers to random instead of sequential. That means that from that time on, all new autonumbers will be assigned randomly to minimize the problem of conflicts between the design master and replica sets. A random autonumber can range from a very small negative number to a very large positive number. These autonumbers can no longer be used as your customer numbers or order numbers if these numbers are ever printed or given to the customer to be used for reference. A customer probably will be confused by an order number = -2. If this is going to cause you a problem, you'll need to switch to custom generated numbers. Post back for details if you need them.<<<<
I'd be interested in getting some details. Data will be entering that table through a data entry form and through appending from a second copy of the DB. I want to make the PK a random autonumber but then when the second DB is added to the Master DB have the table in the Master DB assign each record a "prettier" more usable id number. I know this is stupid but I'm not sure how to relate code to a table (there's no before update event for a table as far as I'm aware).
I have an orders database using Ac2K. Main tables are: tblOrders: Has OrderID which is autonumber, key index (no duplicates)
tblOrderDetail: Has OrderDetailID --long integer
Tables are related in one to many relationships on OrderID-OrderDetailID with both cascades set.
Database is split with tables and front end on server. I can't put front end on desktops because users can access from any of over 600 computers in the school district even tho there are not all that many simultaneous users.
Data is input on main form bound to tblOrders. When user puts in required information (Date,Vendor other etc.), I save record and autonumber generates a new OrderID. The subform window then opens and user then inputs the details of the order to the tblOrderDetail. There can be multiple entries here.
Form and subform are linked by their ID fields as master-child.
PROBLEM: System which runs on network with 2-3 simultaneous users is unstable. It works for a while (maybe 10-20 orders) and then crashes. The subform window is blank (never opens).
When I examine the tables directly I found these problems:
1) Two of the OrderID's (autonumbers) are the SAME! They are usually right above and below each other and usually tied to the same user altho other order info may be different. I don't see any data "collision" if it's the same user. I thought this was IMPOSSIBLE.
2) There is an entry or two in the tblOrderDetail with ######### in one or more fields. The OrderDetailID field is blank. I once found the ######### in the tblOrders as well.
3) The key index is removed from the OrderID.
4) When I go to the relationships window either the link is missing or the cascade and integrity are no longer active. If I try to re-establish, I get a message about missing key index or unmatched data. If I then go and DELETE the records with the ##########, I can re-establish the relationship. And it works for awhile again.
5. Compact and repair doesn't fix anything.
6. One user "thinks" she saw a quick message about "time-out". Is there some setting in Access that I need to change (Tools-Options-Advanced?)
ANY ideas would be much appreciated. There is nothing worse than trying to work with a system that works most, but not all, of the time.
Oper sys is XP. Office 2000 version of Access. Extensive experience with Excel but very little with Access.
I'm creating an astronomical database that includes a function that calculates the distance to galaxies in millions of light years. These databases are massive. Total database has about 1.6 million galaxies (100 Meg). My query concerns about 500,000 of these.
Have the original database and done the query on the objects of interest. It includes the cacluations. All perfect.
Now, it is possible to save just the query so that I can copy it onto a CD and take to the observatory for field reference. Query has about 500,000 lines, one for each galaxy, but is only six columns wide. But at 500k lines it cannot be exported into Excel. Or at least I don't know how to do that.
I have spent a few days coding a database search page consisting of various comboboxes and textboxes. the user can select or enter data in these and when he/she clicks the search button an SQL select statement is generated on the fly and updates an existing query and this is used to query the database. I want to be able to give the user the option to save the query he/she generates and reuse it. How can I prompt him for a name and turn the SQL string strSQL that he has generated into a query and save it for later use?
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version. Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements. The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version. Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements. The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
