Queries :: How To List Records That Aren't There Yet
May 15, 2013
I'm new to query design. I have a database where operators will enter readings from 14 different compressors, twice per shift, three shifts per day. In the Rounds table for each record the following fields are included:
Timestamp (includes date)
CompressorID (1-14)
Shift (1st, 2nd, 3rd)
Round (each set of readings is a round, each shift is supposed to do 2 per shift, the rounds are numbered Rnd 1 and Rnd 2)
Status (this is one of many readings they will record for each round/machine)
On the paper form it is easy for the operator to tell which machine they haven't recorded a round for yet because the space for it on the form is blank. In the Access form they are simply presented with a blank space to enter a new record. I'm trying to develop a strategy to make it easier for the operator to see at a glance which compressors they haven't entered readings for yet. Management also needs a weekly report of which rounds were done and which were missed.
I'm thinking of creating a datasheet from a query that will list the missing rounds for each compressor/round/shift based on the current date. I think I can put that datasheet onto the form the operator is using to record the readings or perhaps on a subform they can access with a button. I'm having trouble designing the query. So far I've created a query that lists all the rounds for a specific date, then another query that uses those results to list the compressors that are missing a record.
View Replies
ADVERTISEMENT
Nov 26, 2007
I have a table that has several fields that contain a date if a user is licenced to use a certain piece of equipment and is null if not. Each recor has other data in it such as name, and a unique identifier (numeric). What I want to do is make a list of the fields which have a date in them to create a licence for what they can operate. Any help on which way to go...pretty new at access.
View 14 Replies
View Related
Jun 19, 2015
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database
Option Explicit
Private Sub cmdAddRecords_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
[Code] ....
View 13 Replies
View Related
Apr 23, 2013
I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.
I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.
eg:
keywords:
look
billy
magic
list:
"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"
my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".
the sheet has the keyword in each row and next to the column:
=COUNTIF(list,CONCATENATE("*@",B13))
where "list" is the external data.
View 3 Replies
View Related
Apr 20, 2015
I have list box named [lstItems], a table named [tblItems] and the field name is [Model]
I would like to (from a list box) delete a record from a button. I have looked and tried lots of delete code, but none of them seem to work.
View 14 Replies
View Related
Jul 11, 2013
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
View 7 Replies
View Related
Dec 11, 2014
I'm selecting multiple records from a list, now I would like to export these selected records to excel.How do I do this?
View 2 Replies
View Related
Sep 15, 2013
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
View 1 Replies
View Related
Feb 25, 2008
Hi guys,
i have built a query to find the sums of various fields from various tables. i want to make ONE piechart from all these the sum values... i.e.
Cars - 500
Planes - 300
Those digger things i always wanted as a kid - 200
But the stupid MSchart activex thingy in Access 2007 always wants to Sum/max/be an idiot to the data. All i want is a damn pie chart!
Any ideas?
If it can be done in VB by saying "split the piechart into 3 pieces. One = Cars, two = Planes, three = diggers" - that would be ace
View 5 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Jul 14, 2006
I seem to remember that there is a way, in a combo box, of entering an entry that isn't displayed in the combo box, so that you can then process that entry into a form's underlying table?
I think the example of doing this was in the MS Samples database for either Access 97 or Access 2000.
Does anyone recall what I'm talking about? Second question, does anyone have a link to those sample databases? Our technicians didn't bother loading the samples for us, so I'm kinda stuck.
Thank you tremendously to any and all who reply..... I'm looking forward to your responses!
View 4 Replies
View Related
Jan 26, 2005
Hello
Here's the problem:
I'd like to print multiple reports using a button in a form.
I guess I could use something like that:
DoCmd.OpenReport "report1", acNormal
DoCmd.OpenReport "report2", acNormal
DoCmd.OpenReport "report3", acNormal
DoCmd.OpenReport "report4", acNormal
etc
in the command.click sub but here's the tricky section:
Some of my reports (they are all based in select queries) do not contain data.
How can I make access print only these forms that include records ?
For example if the query that provides records to the "report2" returns nothing, I would like on the commabd click event mentioned above, only the report1,3and 4 to be printed. Am I asking too much?Hope you guys understand what I'm talking about. Sorry , if my English isn't too good
Thank you in advance
View 7 Replies
View Related
Dec 18, 2013
I'm creating a form where people can choose something in the first combo box [ComboBox1] which results in the second combo box [ComboBox2] displaying only the items that are associated with the first combo box [ComboBox1].
In my second combo box I have the amount of "empty lines" that equals with the items associated with the first combo box. But there is nothing displaying. No words.
View 8 Replies
View Related
Jul 31, 2013
I have a split database. My backend Relationships aren't updating to my front end. How do I get my front end relationships to update to what my back end looks like? All tables are LINKED not imported.
View 1 Replies
View Related
Mar 28, 2014
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
View 5 Replies
View Related
Jul 15, 2007
Hi,
My database has 4 tables. Table 1 is assembly line 1 data. Table 2 is the sizes for assembly line 1. Table 3 is assembly line 3 data. Table 4 is the sizes for assembly line 2.
The table data comes from another program - they are reimported regularly and so I cannot change, add or delete fields from these 4 tables.
I have to change tables 2 and 4 to crosstab queries and then create 2 queries that join 1+2, 3+4. That's easy enough, no problem.
But then I have to UNION queries 1+2. While 95% of the fields are the same, the most crucial are not - the sizes. Assembly line 1 has 5 less sizes than assembly line 2. I need ALL of the sizes to show in the combined query.
I need to add these 5 fields without adding them to the tables. I did manage to create a crosstab query that included the missing sizes, but now I have a 1 in those fields where I need a NULL or Zero value.
Does that make sense? Can anybody help me?
Thanks in advance.
Rhet
View 2 Replies
View Related
Oct 9, 2013
We have an ActiveX grid control, 10Tec iGrid, written in VB6. One of its method, Group, used to group rows, raises the AfterAutoGroupRowCreated event so the developer can adjust the look and contents of every group row created during the automatic process of grouping.
When our ActiveX grid is hosted on an MS Access form and we call the Group method while populating the grid in the form's Open event, the AfterAutoGroupRowCreated event isn't triggered. But this event definitely works as expected in other development environments, and even in MS Access if we call Group from the form's Load event.
Is it a well know issue of MS Access, when any events of ActiveX controls aren't triggered while "executing" the Open event
View 1 Replies
View Related
Jun 23, 2015
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
View 2 Replies
View Related
Sep 20, 2006
Help - Why don’t my date fields sort in date order ?
I’m having a very annoying problem with my date fields. I have a database that keeps records of patients details and their visits to the practioner.
I have a form based on Patients table with name address etc, and a subform based on a Case Details table with details of visits and advice given etc.
I need the Case Details subform to display the latest visit/record at the bottom (the earlier visits at the top of the list). For some reason I just cannot achieve this. Dates seem to be scattered all over and I can’t understand why ??
I have tried sorting the Case Details table in ascending order, sorting the ‘Date of Visit’ field on the form in ascending order (which does work but as soon as the form is closed and re-opened, all the dates are mixed up again).
In the ‘OrderBy’ property of my Case Details Subform, it says:-
[Case Details Table].[Date of Action]
which according the help I have tried to look at should sort the records ??
I’d really appreciate any pointers as to where I’m going wrong
View 4 Replies
View Related
Feb 8, 2005
I'm stuck and have no Idea how to start this, help would be nice
Here is what I am trying to accomplish, I have a Form that I input infromation into several fields (Including the Key field) that will create a new record in my table, Then I click a button on the form and it clears most of the data from the fields and allows me to create a second new record.
What I also want to see is a subform that shows all the new records that were created only while the form was open. Once the form is closed the subform is reset. If I open the form again, the subform will be blank and start listing only the new records i create in that session.
Is this possible? How do I even start this?
Thanks for your help. Please keep in mind this is only the second db i have created and I am not that great at writing code.
View 2 Replies
View Related
Nov 30, 2005
I have a table with records Name1, Name2, through Name6 I need to have a query or something that will join all theese records under one list. :confused:
Thank you in advance. :)
Rene
View 1 Replies
View Related
May 11, 2005
Ok, I'm sorry if this is somewhere else in the forum, but I can't seem to locate it. Here's my task:
I am writing an attendance program for an Ambulance Company, and I actually have a large chunk of it done. I have a text box for the date and a combo box for the type of activity. I have two side-by-side list boxes that lets the officers select peoples names and put them in the right side list box. The part that I need help with is: How do I add new records to the table that includes each of the members names, but all the same dates and activities.
Ex.:
John Smith 5/10/05 Meeting
Jane Smith 5/10/05 Meeting
Tom Jones 5/10/05 Meeting
I think that i need to write a loop, but my access programming is way out of shape. Any help would be appreciated, ladies and gentlemen
~Damon Vogel
View 4 Replies
View Related
Jun 2, 2006
(Been a while since I had a question.) I have a table that stores a list of paper forms. the fields are ID#, display name, doccument name, and a code to determine if its a Word or Excel doccument, or an access report. Works fine; the user can click a form name (on the Forms/Reports tab on the main menu form) in a list box, set the # of copies and print the form. There are cases when a group of forms is needed (client admission is one). I have made a group table containing: GrpID, Grp Name, formID. I would like my "Form Group Edit" form to have 2 list boxes; the left one containing the (short) list of available forms; the right one displaying what forms are already (if any) in the selected form group. My idea is to have add/remove controls to manage the right list. I know this "wheel" is already invented and would like to plagerize one that works. Thanks in advance for your help.
View 2 Replies
View Related
Feb 4, 2014
If i have a drop down list containing employee type names e.g. Plumber plasterer bricklayer from the employee type table. How would i select the bricklayer and bring back records associated with the selected employee type . I have done a query to go and get the records for the employee type and it works for one employee type.
But I cannot get the button to go and get the records when i select different employee types from the drop down list. I could add loads of buttons with the same query and change the employee type but looks a bit crowded and untidy. Am i missing something simple or is it just complicated.
View 5 Replies
View Related
Aug 18, 2015
I have an unbound listbox and some unbound text boxes.I am attempting to create code that will fill in the text boxes depending on which record I select within the list box.The listbox does not have multi select on, only single select is possible.I found listBox.Value which gives me the primary key which is nice, its something.
I have attempted several ways of things I have found online with no luck on how to get the remaining information into the other text boxes.I have seen a for loop to find the one that is selected then using the listboxControl.Column(intColumn,intRow) but I obviously don't understand how to implement it.The other option is to use a query but I am apparently doing that incorrect as well.The query:
Code:
"SELECT [Student].[firstName] WHERE [Student].[SSID] = studentList.Value;"
I think this is the correct query but I cant seem to implement it either.
I know you don't want people saying "tell me how to do it." So if you have any resources I am good reading up further on it, I just cant find a place that I can get the required info.
View 4 Replies
View Related
Nov 30, 2013
I just have a question on making an interface for my database.Some sample databases and office templates have in them, a list of the names of all the records embedded directly on a form, where you can navigate from one record being displayed to another.
An example is in the contacts database template that you can get from Microsoft. It features, on the left, a list of the names of all the people in your database, alphabetically. So, all you have to do is click on John Smith, and bam, John's information will appear in the fields on the right side of the form. If you want to move to Claire Jones, all you have to do is click her name and her information will appear in the fields on the right.
View 6 Replies
View Related