Another "what Kind Of Query Is This" Thread

Oct 22, 2007

I have a Customer table in an Accounts Receivable late-payments database that has very basic fields:

CustomerNumber, Name, BillToAddress1, BillToAddress2, City, State, Zip, Phone

As we get changes from new invoicing activity, I need the table to update the contact info. So, we get a spreadsheet each day with new invoicing activity, some of which will already have customers in our database. I need to capture any new addresses or other contact information to update my current records. We append the new info to the Invoices table, then need to grab the new addresses and update the Customer table.

I don't know why I don't know how to do this, as it seems so simple. Any help is greatly appreciated.

View Replies


ADVERTISEMENT

What Kind Of Query Is Needed For This?

Aug 13, 2007

Hello all,
I need help with making a query this will check the OrdQty for all items in a specific order against the Sup1inv field, then if supplier 1 can not fill the order have it check against Sup2inv field.
Here is an example of the data im working with

Order# Item# OrdQty Sup1Inv Sup2inv
555 widget1 2 1 3
555 widget2 1 1 4
556 widget3 1 3 0
557 widget1 7 1 3

The part i am having trouble with is making sure the query "understands" that both items on order 555 are the same order so the end result would tell me that order 555 needs to be placed at supplier 2, not 1.

Thanks in advance for the help,
Eddie

View 6 Replies View Related

Sum Thread

Mar 9, 2006

I have database where users can produce a quote. Each new quote gets a quote number,
when users add a item to the quote, they also add a DHR (daily hire rate)
The table records the item and adds the quote and the DHR.

I want to run a query so it shows the quote number and adds all the DHR's for that quote.
Can this be done in a query as an expression.

View 4 Replies View Related

Deleting A Thread

Mar 14, 2008

An individual posted a new thread today (3/14/08) (believe it was in the General Section) asking for a help with a UDF that wouldn't process. He received one response, then I came in with another response which I later edited to include more info. Saved it, checked back to ensure it was what I wanted -- it was.

Closed the browser, shut down the computer. When I later reopened this site, the thread was missing entirely. Did a search on my recent posts -- it's not there.

I'm baffled -- how does a thing like that work?

Bob

View 3 Replies View Related

Difference Between Post And Thread

Mar 15, 2007

Hi guys am not sure if i got dis in d correct forum (forgive me if not)

Just wanted 2 know what's d diff btwn a thread & a post (if d's any)

Noks

View 6 Replies View Related

Yet Another Thread On Date Queries

Jun 28, 2005

I have a table that stores date/time in the Now() format, but I want to run a query that will format the date/time in "mm/yyyy" with MSAccess SQL.

Here is an example table:

Table1
------
IDDate
11-Dec-2004 12:24:06 PM
24-Apr-2005 5:04:18 PM
38-May-2005 8:13:52 PM
413-May-2005 2:27:57 PM

Here are the desired results:
Table1
------
IDDate
112/2004
204/2005
305/2005
405/2005

View 1 Replies View Related

Yet Another Insert Image Thread

Mar 10, 2005

Hello,
Although, I have seen many threads in regards to displaying images in forms, I have not been able to find exactly what I'm looking for.

The solution I am looking for is to give the user the ability to browse for an image, select it and insert it into the form. From reading these forums and playing around with Access, one thign I am sure about is that I want to link the image (I couldn't believe how bloated access gets when embedding an image).

I thought of a great solution for linking images, but I have no idea how to achieve this. (This solution, in my opinion would be great not just for images, but for all kind of objects).

Since I am linking the image, I would like to be able to store the image in a "private database" folder, that way the image will not be tempered with. It is my understanding that if I wanted to do that, I would have to first place the images in that folder and then actualy insert them into the form. This seems kind of pointless to me, since it will create extra work for the user.

So basically what I would like to do in pseudo code is:

On Click, browse for image
on image select, save image to \database image folder
link image to \data image folder
display image in form


I think this would work really well for all kinds of document management (not just images) allowing the users to attach pdfs/images/documents to records with out worrying where they are stored, and knowing they will not be tempered with (I'm planning to place the folder somewhere on the server)]

Any ways, any help woudl be greatly appreciated, and if I do manage to come up with a solution on my own, I will be sure to post it here.

View 1 Replies View Related

My Last Thread Today, I Promise!--go To Record By Id

Jun 10, 2005

hello once again,
i'm using this code to go to a certain record in a table:

Dim strRecord As String
strRecord = Me.List26.Value
DoCmd.GoToRecord acActiveDataObject, "Stopwatch", acGoTo, strRecord

the problem with this code is that it goes to the record number, not the id.... how can I tell it to go to the record it (primary key) instead of the absolute position number of that record?

View 2 Replies View Related

Is Relations A Kind Of ERD

Jan 12, 2008

After designing the database that mean after creating all the tables and when pressed on relations buttuon it shows a diagram with relations established. Is this a kind of ERD. in this it shows relations between entities so can we call that an ERD?

View 3 Replies View Related

Is This Kind Of Count Possible?

Jun 20, 2006

Hi,
Im very new to sql so please excuse my ignorance, I ve been trying to do this for a while and not getting anywhere:
I have a table that has customer and product data in it. Its super market data.
I have a field that has customer education level (education), this holds text, (bachelors degree, graduate degree, high school degree, partial college, partial high school)

and a field low_fat which is boolean (0 or -1), wether the product purchased was low fat or not.

Im interested in the realtionship between education level and number of low_fat products purchased.

I'd like a resultset that counts the number of low_fat products purchased for each category of degree level. Something that looks like this

EDUCATION Low_fat_Count
bachelors degree 500
graduate degree 234
high school degree 124
partial college 333
partial high school 124

Is this possible?

So far I've come up with this:

SELECT education, COUNT(*)
FROM AllTablesMerged
WHERE low_fat=-1
GROUP BY education;


but access asks me for specify parameters for education and low_fat.

Thanks in advance for any help on this!

View 2 Replies View Related

Special Kind Of Calendar

Aug 28, 2006

Can anyone give me a direction for the following problem please :

I would like to create a yearcalendar that might do the following job for me:

I have 12 firms with different amounts of people working for them. We need to see those people once a year and give every firm an amount of days that they can come see us. Every firm may send 5 people each day but we can see up to two firms per day. So we receive 10 people on a daily base but only a max of 5 belonging to one firm.

I would like to have a means of automaticaly according dates to those firms over a period of one year but I must be able to exclude or instance, week-ends, holidays and so on...

I hope you can give me a start because I realy don't know where to begin

View 1 Replies View Related

How To Set Up This Kind Of Master And Sub Form?

Jun 14, 2005

There are total three tables tblInternal, tblExternal, tblCourses. Table
tblInternal and tblCourses is one to many relationship by reference foreign
key InternalID from tblCourses to tblInternal. If I just use these two table
to set up Master form and subform in ACCESS, you can in subform Field Linker
window of subform property Data tab select primary and foreign key to
generate link between Master and subform. So if you select user name from
tblInternal in Master form ComboBox, you can see the detailed information the
user relates to.

Same relation between tblExternal and tblCourses.

Now I want to merger tblInternal, tblExternal to list user name in ComboBox
by Union statement. But in SubForm Field Linked window I just see one primary
key from one of these two tables instead of two primary keys from each table.

How to solve this issue?

View 1 Replies View Related

Protecting The Database - What Kind Of Solution?

Oct 11, 2005

Hi there,

I have built a Access solution for a music school, It was installed on 3 machines.

I'd like to protect my database from installing onto another machine without my permission.

I did install database as a mde file so they cannot see my codes. However, if they copy the database to another machine (esp. another machine in different school) they can use my software without my permission. How can I prevent this? If they copy the mde file into unauthorized machine, database should work as a demo version (such as limiting the number of records in tables to 10). How can I do this? What should I check, hd id, mainboard serial or what? Is there any ready solution (at least modifiable) for that kind of problem?

regards

View 14 Replies View Related

Relationship Problems... Of The Database Kind

Nov 29, 2007

(see sig for details on what I'm capable of before you toss code at me ;P )


Here's my current setup:

Table1(Assignments)
Instructor <related to Instructor table, pulls data from there)
Academic Year
Fallcourse# (where # = 1, 2, 3, 4), (course pulled from Courses table)
Fallcourse#comp
Fallcourse#notes
Wintercourse#
Wintercourse#comp
Wintercourse#notes
Springcourse#
Springcourse#comp
Springcourse#notes

Table2(Courses)
Coursename (unique)
Coursetype (required, elective)
Courselevel (ugrad, grad, MAS)

Table3(Instructors)
lastname
firstname
empID
rank

The purpose of the database is to assign courses to various professors and append supplementary compensation and, if necessary, notes to each course assignment. Each professor can be assigned up to 4 courses (or something to take the place of a course) per quarter.

My problem. I have created a quite a few reports with this setup including an academic year schedule, compensation reports for the year, sabatical / course release listings, etc. I am having trouble with what I am hoping to be my final report.

Requirements for the final report:
List of all courses for an academic year (filter by year)
group by level (easy)
group by type (easy)
append instructor's name to the end

Report/Relationship Problem:

I can't seem to set the relationship to allow more than one field's data into the report. I can only show courses from the [Fallcourse1] because I can't relate multiple fields from the "Assisgnments" table to one field in the "Courses" table. Effectively, that's what I want to do, but my mind's burnt and can't seem to figure it out.

Maybe you guys could help me out? Suggestions, questions - all welcomed.

View 6 Replies View Related

Kind Of Countdown Reminder In A Date Column

Jun 29, 2007

hey,

Sorry to open with a question but its usually the way, i'm quite an experienced computer user but never used any office products before and just thought i should learn as i'm losing money every week by not keep track of things properly.

So then to my question..

I've made a table to cover all the orders i have had placed, this includes a date column for when that order was placed. I just wondered if it would be possible to have some kind of system where after 7 days without any acknowledgement from me it either pops up or turns red or something similiar. I'm thinking the acknowledgement could be the yes/no box and i tick that when i recieve the item back, if i dont it either pops up or turns red or something :confused:

any help much appreciated

View 2 Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

Mar 4, 2015

In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.

All is well and fine until source data changes and the pass-through query runs too long and times out.

If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table

Is there a way to open an Append or Make-table query in Design view without invoking the source query?

View 1 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Queries :: Crsosstab Query Doesn't Recognize Expression In Source Query

Aug 12, 2015

I have a query where these are the fields:

ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC

The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)

When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?

View 4 Replies View Related

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

Jul 15, 2014

I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?

View 9 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

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

Show All Records From One Query And The Sum Of A Field In A Related Query Record

Mar 8, 2005

I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.

That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.

I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.

What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.

Can someone see where I’m going wrong?

Slaughter
slaughter at mizzou dot edu

View 9 Replies View Related







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