Queries :: Quotations Database - Queries Showing Too Many Records
Jun 25, 2015
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code: SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID WHERE (((tblMaterialSpecifications.ActiveInactive)=-1)) ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection Dim recordst As ADODB.Recordset Dim strSQL As String Dim strPath As String Dim appXL As Excel.Application Dim wb As Excel.Workbook
I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.
This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?
I am looking for a way to either display through a query with an expression or any other alternative that someone may have to display the last 100 records entered maybe based on date entered or something.
At the moment i have a query and report going form start date to end date but would also like a query/report that will just show me the last 100 records entered.
I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).
I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.
My Top 20 query is below
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS FROM MASTER WHERE (((MASTER.AGE)>=30)) ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;
I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.
If you take a look at the tab called insurance i have a list box. How do i filter the list box so that it will only display the patients insurance based on the demo tab.
Also i have other records in the table called people. they are not showing in the people form. when i use the mouse wheel automatically creates new record please help
I am building a stakeholder database in Microsoft access 2010 and I want to be able to say that a stakeholder attended an event. I have managed to do this but I can only say that one stakeholder attended an event at one time. This is quite a problem as there can be up to 800 or even more stakeholders attending an event so to go through and click each one would be very time consuming.
My current set up is I have a stakeholder table an events table and an event attendance table. It all works fine apart from only being to edit one record at a time. I have tried update queries with no success, I can attach the database but would need to remove the data for data protection reasons.
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
I have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.
The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.
I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.
Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?
I am working with an inherited database. When this database was created, a large amount of data was imported. Over the course of time, I have added additional fields for tracking information. One such field is "Date Started."
Unfortunately, there are almost 500 records without this information and that is skewing some report results.
I would like to do is insert the date of 9/9/1999 into all records that have no data in this field. (This date is well before the creation of the database and would serve to indicate old records, whether or not they are still active.) Copy and pasting isn't working, and I can't do a find and replace, since there's nothing to find.
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
On another thread in the forum I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books.
SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id GROUP BY Pupils.Id, Books.Book HAVING (((Books.Book)<>"Blank"));
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description CarNum SerialNum Category Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).
A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).
I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...
My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case. Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:
1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler) 2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)
I am trying to run a query and for some reason its not showing the data from the table (its blank), this is a monthly reporting I do - last months query works perfectly.
Table: log and list
Here is the SQL
SELECT log.*, UL.langue, UL.version FROM log, (SELECT list.id, list.[langue], list.version, list.no_joueur FROM list GROUP BY list.id, list.[langue], list.version, list.no_joueur) AS UL WHERE (((log.id)=[UL].[id]) AND ((UL.no_joueur)<90000000));
I am working with a database and existing query from my predecessor. The field in the query appears in both the database table and the query.when I run the query it doesn't appear. Is there a limit to the number of columns in a query I easily added a new column and moved a column,
