Search results

I am building a database for generating quotes for a service shop and am struggling with how to handle our flat rates and special rates for customers. There is a fixed list of tasks that are considered a flat rate, stuff like Visual Inspection, Pressure Test, Steam, Sensor Inspection... and so...

Hello, I am designing a database for a tank trailer repair shop and need a bit of guidance on how to structure my tables. At a high level, the user will input a Work Order# and all relevant info into a parent form (customer, PO#...etc). They will then choose tasks on a subform (changed...

I have a criteria in a DCount that says "MinOfDateout < #" and have no idea what < # is doing. MinOfDateout is a field in a query. The entire string looks like this: UNICompaniesSupportedCumulativeAB = Nz(ECount("[MaxOfContactID]", "QryCumulativeMetricsNew", "[State/Province] = 14 AND...

I have a form that shows project details including some financial information related to my project. In order to update the financials, or add some transactions, I click a button that opens a transactions form. On that form, I enter the data required and then close it to return to my project...

I have this query expression that gives me a #NUM! error due to div/0. PercentPaybackToDate: FormatPercent(((Sum(IIf([TransactionTypeID]=5,Nz([Amount]),0)))/(Sum(IIf([TransactionTypeID]=9,Nz([Amount]),0)))),0) I can't for the life of me figure out how to test for a zero value in [Amount] to...

Hello, I have a dataset that includes Company Name, Date and Program Type. I need a query that will list distinct company names in rows with 1st, 2nd, 3rd ..etc engagements as column headers and the program type as the data. Please see the attached image for a clear picture of what I need to...

Hello, I am using the following code to pull data from multiple spreadsheets in a directory tree in to one master sheet called "Results.xlsx" I would like to modify the code to only pull data from Row 3+ (until a blank row is encountered) of every sheet that contains data except the 1st...

Hello, The other day I could swear I came across an article that said a feature of Access 2016 was that you could view all of the spreadsheets that are linked to your database. Was I imagining things? I can't for the life of me figure out how to do this.

So I have a query that selects 3 fields: Program Type, Transaction Type, and Amount. The query picks up and sums 2 different transactions, Budget, and Commitment. The result of the query would look like this: Program Type | Amount | Transaction Type CSS | 100 | Budget CSS...

I have an access 2010 database that is split into FE and BE. In Excel 2010, I would like to link to a query that resides in the BE of my database. What is the "best" OLE DB provider? 12.0 Access Database Engine OLE DB Provider? 15.0 Access Database Engine OLE DB Provider? Does it really...

I have a field that calculates a date range from a record in my query. Date: Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1 My problem is that if TransactionDate is blank, it returns #ERROR. I am linking this query to Excel, which means I cannot use the NZ()...

Hello, I have a database that tracks projects and quarterly reports for those projects. Each report I create has a set number of transactions associated to it. I have attached a picture of the current relationships and just wanted to get some feedback on whether I have done it correctly. I...

Hi All, I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that...

Hi All, I am trying to count the number of times a client has engaged with our company. I have a company table, a reservation table, a rapid prototyping table and a project table. There is a one to many from the company table to each of the other 3 tables as can be seen in the attached...

Hi All, I have the following SQL which returns rows of distinct numbers that are calculated from a field. SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls FROM tblProject; ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307 I need to count the number...

Hey All, I have a database that tracks projects and companies. I have been asked to add a table that will track contracts that are either associated with project OR with a company. I have made a very simple data model that is attached to this post that will hopefully help explain better. I...

I need help creating a cross tab query that will calculate the value of a person who is hired and terminated between 2 dates. What I currently track in a table is the date someone is hired, and the date someone is terminated. We call these people HQP - highly qualified people. Every quarter...

I have a form that shows a companies engagement with our company. If someone wants to edit the details of that company (address phone number etc...), they click the "edit" button, which then opens a form where edits can be performed. After the edits are done, the user clicks a save and close...

Hello, I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry...

I have a bound form that is used to enter company info (address, name, category...etc). When the user closes the form, if Company name, province or category are left blank, I warn them with a message box asking if they want to exit and undo changes, or return to the form to fill in the missing...