I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
I am writing a module to send information to Excel, and the information is based on a Crosstab query. But I keep getting a "syntax error in the TRANSFORM statement".
Function Edt_Royalties() As Integer Dim AplicExcel As Excel.Application Dim Planilha As Excel.WorkSheet Dim stArq As String Dim DB As Database
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
I have a database where by users can input images. The database then copies the selected image into the current working directoryimages (c:dbimages as an exmaple) the it keeps a reference to the path in a table. When users then browse the records on the form I have an ImageFrame which looks up that path and displays the picture. This all works fine.
The problem I have is that sometimes the working directory changes. I then have problems with the previous images inputted as the tbale where the reference is stored is still pointing to c:dbimages. How can I can it so that rather than have a fixed path to c:dbimages the database will just looking in the current application directoryimages for the pictures?
use the same referenced ID more than once in the same table? I have a database for my stamps. The exact same type of stamp may be kept in more than one location (Album). So, I have TBL_Albums which has two fields "AlbumID" and "AlbumName". Can I use "AlbumID" in my main RecordSource "TBL_Main" more than once to reflect the different Albums this stamp may be found in without causing any major problems to the database?
If this is okay, I could then assign different alias names to each "AlbumID" column in "TBL_Main" such as Location1, Location2 etc to be able to tell them apart on forms etc.
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
AgreementTable PK:[Lookup]CustomerName from Customer Table:Agreement#
AddendumTable PK:[Lookup] Customer Name:Agreement# from Agreement Table
So, Each customer may have more than one agreement. Each agreement may have more than one addendum.
So when I create a table to create a new addendum, naturally I would like to link this to an agreement (which has already been linked to a customer). When I do the lookup wizard for the agreement field however, instead of being returned a list of customers, i get the PK of the customer and not the customer name.
How do I get the lookup to show the customer name and not the PK.
I have a main table which records employee,date,record employee is related to tble employee. i want to be able to delete an employee however keep the referenced records with the name, date, record, is this possible?
I'm creating an amortization schedule database using Access 2010.
I designed it such that I'll input actual principal and interest amounts in tbl_LoanActivity when I get statements in. I'd like to be able to press a button to update forecasted principal and interest amounts, then later I'll add code to make a report pop-up.
I initially designed this with only one loan to see if I could make the thing work, and it did. I've since added another loan and made adjustments, and this is where I'm having an issue.
tbl_LoanActivity references tbl_Loans, which has interest rates and monthly payment amounts. Needless to say tbl_Loans is the RecordSource for frm_Loans, and as such has a field LoanID. I have two queries that need to reference [Forms]![frm_Loans]![LoanID]:
I can run these 2 queries (individually) with a reference to frm_Loans.LoanID just fine. (Without that reference, forecasted principal and interest numbers are highly inaccurate.) I have a qdf append query (vba) to calculate forecasted principal and interest amounts based on the most recent principal balance (referencing queries above), and relevant data in tbl_Loans (InterestRate, and RecurringAmount).
So here's the problem: without the above queries referencing [Forms]![frm_Loans]![LoanID] the qdf runs, yet produces inaccurate numbers. With the above queries referencing [Forms]![frm_Loans]![LoanID], Access gives me a message "Too few parameters. Expected 1." If I take the qdf and put it into a standard query it runs just fine. I need this append query to be a qdf so it will loop until RecordsAffected = 0.
I've sterilized the data and attached the database. I was thinking this adjustment of adding the reference to [Forms]![frm_Loans]![LoanID] in the 2 above queries would work, but it seems like the qdf is not inheriting the parameter.
I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible? Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.
I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.
I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.
I have my Query looking at multiple colums on a single table.
The table has approximately 2000 records.
I would like to create a Query that searches for criteria in one colum only. I have created a Query and providing i search for exactly what is written then it shows results, e.g. when i seach for Horse Furniture then i get many results but if i search for Horse i get no results?
I would like the search to be dynamic, so when the end user runs the query they are presented with a dialog box asking what they wish to search for (this i have no problems with) however i need to know how to get my query to be dynamic and provide results based upon single information.
is this possible and if so then how?
Thank you in advance for your help, i have been banging my head for a while on this one and am getting no where.
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE: UNIT INF_MONTH INF_YEAR PT_DAYS CVC_DAYS IUC_DAYS VENT_DAYS APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT INF_STE_MAJ SPECIF_SITE INF_MONTH INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE;
----------- qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds. 1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of? 2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
I am trying to create an update query. I am trying to update a field in a table with the current date as a request.
I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:
Private Sub Command39_Click() Dim t1 As Date t1 = Date db.Execute("update tblTest set tblTest.Date2") = t1 End Sub
But when I press the button I get: Run time error '424' Object Required
