Referenced Crosstab Query Does Not Always Have Information Required
May 11, 2007
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
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)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
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?
View Replies
ADVERTISEMENT
Feb 10, 2012
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
[Code] .....
View 1 Replies
View Related
Sep 21, 2007
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
Please help!!!
View 2 Replies
View Related
Sep 28, 2006
Hi,
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?
Many Thanks,
View 2 Replies
View Related
Nov 2, 2012
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.
View 13 Replies
View Related
Nov 3, 2014
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?
View 3 Replies
View Related
Feb 14, 2007
OK, Here is my issue:
CustomerTable:
PK:CustomerName
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.
View 1 Replies
View Related
Aug 18, 2014
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?
View 3 Replies
View Related
Jan 15, 2015
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]:
1) qry_LoanActivity_MaxDate,
2) qry_LoanActivity_withPrincipalBalance
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.
View 5 Replies
View Related
Jun 4, 2012
Do fields in Access Tables have a 'Number' property that you can use to reference them?
For example, if you have the following fields:
EmpID
FName
LName
Department
Hire_Date
Is there a way to query an employee's Hire_Date by telling Access to return the value that is in the 5th field [Hire_Date]?
[URL] ....
View 8 Replies
View Related
Apr 12, 2013
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 both relationships to be 1 to many.
View 1 Replies
View Related
Sep 13, 2007
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.
View 2 Replies
View Related
Oct 8, 2013
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"
View 3 Replies
View Related
Jun 27, 2012
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?
View 1 Replies
View Related
Sep 12, 2005
Hi All,
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.
The tables (simplyfied) are :-
Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05
Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90
Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100
The table that I would like to create would contain Code, Date and Stock.
eg.
Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........
102 12/9/05 197 ...........
Any ideas/direction would be welcome.
Carl
View 4 Replies
View Related
Dec 10, 2006
Hi,
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.
Bev:mad:
View 2 Replies
View Related
Mar 28, 2007
Hi All
Im after some help with a query im building.
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.
Please can anyone help?
Many Thanks
Dazstarr
View 5 Replies
View Related
May 17, 2007
Hi
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.
Can someone please help me.
Many Thanks
View 4 Replies
View Related
Jan 29, 2008
I have a table set up for training with the following fields. Date, Employee Name, Class Name.
I have entered all the current data. I want the database to tell me who doesnt have a class completed.
I also would like a code to put in my query that will show recurrent date, although there is different recurrent dates dependent on the class.
Any help would be appreciated!
Thanks in advance.
View 4 Replies
View Related
Jan 11, 2008
Hi everyone.
What I currently have is 2 columns in one table 1 is required and 1 isnt.
However what i need to do is work out the sum value of the greater amount from each line.
IE
A B
1 2
1 0
2 1
which would give a total of 5 as its adding B1+A2+A3
I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas
=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])
my plan was to get the "IF column to show me the higher amounts which could then be totalled up.
Any ideas where I may be going wrong?
Kev
View 12 Replies
View Related
Apr 27, 2007
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.
View 2 Replies
View Related
Nov 7, 2014
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 Tables are joined Left to Right as Follows:
UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR
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.
View 14 Replies
View Related
May 31, 2006
: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;
Thanks for you time! :)
View 1 Replies
View Related
Aug 12, 2014
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.
View 9 Replies
View Related
Dec 10, 2007
Hi
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.
My knowledge of Access is Intermediate.
Kind regards
View 14 Replies
View Related
Jul 10, 2013
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
It highlights the 4th of code....
View 8 Replies
View Related