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

Module To Send Information To Excel Based On Crosstab Query

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

Crosstab Query Based On Crosstab??

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

Referenced Image Issues

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

Tables :: Referenced ID Twice In Same Table?

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

Forms :: Fields Are Required To Be Filled Out Even Though Not Set As Required

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

Help - Lookup Issues (looking Up A Referenced Field)

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

General :: Delete Employee From DB However Keep Referenced Records

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

Modules & VBA :: QDF Not Getting Parameter From Referenced Queries / Form

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

Can Fields In Access Tables Be Referenced By Number?

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

No Unique Index Found For Referenced Field Of Primary Table?

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

Help Required For Query

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

Tables :: Error - No Unique Index Found For Referenced Field Of Primary Table

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

Access 2010 - Getting Form To Show Update Data To A Table When Two Tables Referenced

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

Query Question Or Maybe VB Required

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

Query Search Help Required

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

Query Advice Required

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

Query Calculation Help Required Please

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

Table / Query Help Required

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

Query Problem - Greater Value Required

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

Query Design To Retrieve Required Input

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

Queries :: Getting Required Data Using Query For Calculations To Be Used Later?

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

Blank Fields In Crosstab Query Based On Previous Query

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

Queries :: Query Error When Data Changes In Crosstab Source Query

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

A Query That Returns Values That Total Less Than A Required Amount?

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

General :: Update Query Has Error 424 Object Required

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







Copyrights 2005-15 www.BigResource.com, All rights reserved