How do I access multiple records within a query?
I am trying to calculate the total quantity of components in a nested Bill Of
Materials where the true quantity of any sub-level component is dependant on
the quantity of the previous higher component.
Level Item number Quantity
1 A123 1
2 A124 2
3 A125 2
3 A127 1
2 A126 1
2 A125 1
1 A128 1
2 A130 2
Take a look at:
http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&a...SUMIF help and another query
I'm wondering if someone could help with a problem I am having - not so much
a problem - more I'm not as good at Excel as others here and they may be
able to help.
I'm treasurer of a semi-professional non-league football club. As such I
have to calculate travelling expenses for each player. The tax man taxes
players on home games and training sessions but doesn't tax the travelling
expenses for away games for those players that take their own cars and not
the bus. Currently we pay them 15p per mile plus an additional 3p per mile
for every passenger - I know, not much - ...Update Rollup 3 won't install properly
After running lots of updates on SBS 2008 server, I finally was offered the
Update Rollup 3 to alleviate the problem of my Win7 box not being able to
connect. However, I am getting an error when trying to install the patch.
After about 40%, the install fails with Code 6BA. Also, the "New Updates are
available" baloon pops up in the system tray, offering to install it again.
I've tried restarting the server, but can't seem to install this update. Any
suggestions would be appreciated.
> After running lots of u...2005 Update
If anyone was having problems downloading from Vanguard, the latest update
for Money 2005 seems to have fixed the problem, where shares are subtracted
MVP for Microsoft Exchange Server
...Subtracting values in a query
I have a query with multiple repeating values, each value has a set of
readings assigned to it How do I subtract the max/min from the readings for
each value (looking for change over time)?
Value Reading over time
I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for
value 2; and so on.
Is this possible? Thanks!
Replace 'z' with your [Value] and 'x' ...sub query
Does MS Access support sub query?
I wanted to create a query which has sub query like following
Select MyField, (select MyField1 from mytable1 where mytable1.myfiled2 =
mytable.MyField3) from mytable
Do I need create 2 queries to do this or just one query with one sub query?
Your information is great appreciated,
Yes. Access supports subqueries like that.
More info and some examples:
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at ...Using saved query for different tables
I want to use the same query for many tables. I'm new to Access. I see where
you can save the query and name it, but is there a way to then use it with
different tables? I can't see where that can be done. This would be most
helpful in Update Queries.
You could build the UPDATE statements in code in a dialogue form's module,
getting the variable table names from unbound controls on the form, and then
execute the statement. You could even modify the SQL property of a saved
query in code and then execute it.
The fact that you are contemplating thi...checking queries between programs
I have several access programs that are extremely similar but written for a
purpose at the time
I now wish to combine into one access program
Is there a simple way of checking queries between these programs to see if
they do the same job or not
Hope this makes sense, regards, Garry
...Leading Zeros SQL query
I need to add leading zeros to every item in our RMS database that uses
UPCs. Is there an SQL query that could accomplish this?
Hi wesley ,
I tried this query and it update my itemlookupcode with leading zero where
the barcode format is 9(UPCA) but please backup your data before running this
and before running this ..try to ask for second opinion...I might miss some
files that needed to be updated...
update item set itemlookupcode='0'+ rtrim(ltrim(itemlookupcode)) from item
I hope it helps :)
"wessan...Favorites lost after update
A friend reports this problem:
"Ever since we updated IE, I cannot click on my favorites. The icon
next to each link is the default picture sign. If I click on the
link, it attempts to print."
The system is Windows XP Pro SP3. The update is to IE8.
Any thought about how to pursue this further? I know I need to examine
the system and will do so, but I would appreciate any prelininary
thoughts or direction.
Also, if I roll back the IE version with
would that bring back the missing favorites?
...Manipulating or comparing xs:dateTime values in xpath queries with SelectNodes
Is there a simple way of compare dates? I am trying to create a list of
perishable stock in an inventory that is near or past its expiration date.
I've tried telling it to compare a node's value with a DateTime string, but
that doesn't work.
I've assumed that it is easier to manipulate today's date instead of the
date in my ExpireDate element.
Thankyou in advanced for taking the time to read this and possibly helping
Public Function FromElem...Payroll Tax update
How do I grant rights to DYNSA to do a payroll tax update? For some reason
'sa' is not a user and we have been unable to add it as a user. We are
unable to update our payroll taxes.
Any help with this would be greatly appreciated.
Platform WinXP SP3
When I try to pull down updates from MS sites a window comes up saying that
these sites ate only for WINDOWS not MAC. ie. somewhere along the line my
machine is being read as a MAC machine. I only have as above, no second OS
etc, but for a couple of months I cannot pull down updates.
On Sat, 24 Apr 2010 18:38:38 +1000, "David" <email@example.com>
>Platform WinXP SP3
>When I try to pull down updates from MS sites a window comes up saying that
>these sites ate only for WINDOWS not MAC. ie. somewhere alon...append query 03-03-08
i am trying to append from one table (NewItems) to another(Main), but get an
error saying it can't append 975 [of 4000] records due to key violations. The
target table (Main)has a composite primary key made of date and itemnumber.
All the items to be added have a new date that doesn't exist in Main. The
append query is left joined on NewItems ItemNumber, so that all records from
NewItems table should go into Main table.
What am i not seeing? Why are 975 items being rejected?
doh! i was using an expression to add , so i had to use a max query instead
of the Main table...The query processor could not produce a query plan
SQL 2005 with SP3
Before I added the following view to the SELECT that follows it, the whole
thing has always run fine.
This view gets all the unique dates from a main table (tblNotices) and one
of its subtables (tblNotices_AdjournmentDates)
ALTER VIEW [dbo].[vwLatestAdjSaleDates]
(SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE
NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) AS DateOfSale
NoticeID NOT IN (SE...Money 2007 Deluxe Update ???
When I try to open my money 2007 deluxe file I am getting prompted for an
update. When I click on OK to start the update, Money closes and the update
starts and gets to approximately 17% downloading and then stops and errors
Have tried 3 times and every time it stalls and errors out.
Anyone else having this issue?
Tried it again and this time it got to 99% and did the same thing.
> When I try to open my money 2007 deluxe file I am getting prompted for an
> update. When I click on OK to start the update, Money closes and the update
> starts ...How to display query criteria in my Report?
I have searched but found no answers, so I guess this is standard :P
Anyhow, how do I get a certain fields query criteria written in the report?
(For example, my data source is a query that selects all customers (cust_id)
with annual turnover gretater than 10,000 (criteria is >10000).
Now, sometimes I change the criteria so it would be nice to have it
dynamically turn up on the report (rather than static text). But what's the
"call function" for this?
Mikael Lindqvist wrote:
>I have searched but found no answers, so I guess this is standard :P
&g...Multiple Listbox selectin and query criteria
I've read many of the posts here about using multiple list box selections to
filter queries yet I still cant get it to work. I'll post my understanding
then my code (which I am totally clueless about) and see which is at fault.
My table is called "sales", my query is "sales summary Query", my filter
form is " Filter Report", my listbox is "customerselect" and set to simple
I then have a hidden control named "customerselect2" with on click set to
event procedure, then in the procedure I've pasted the code ...drop down boxes query?
i am attempting to create a drop down box that you can (a)
search in by entering letters ie. type a and it goes to
the a's and (b)can enter you own data in ie. the option
you require isnt there so you can go to a blank one and
enter your own data.
i have seen it on operation before but am lost as to how
to do it.
please can anyone offer any help at all?
many many thanks.
Have you tryed Data - Filter - auto filter
>i am attempting to create a drop down box that you can
>search in by entering letters ie. type a an...Query problem (MS SQL and MS Access tables)
I have a front-end application (MS Access 2000) that uses data from both MS
Access tables and MS SQL Server tables. The SQL tables are linked to the
front-end application via ODBC. I am trying to perform a query using the
1. dbo_430_PRProject (MS SQL Server Table)
2. dbo_430_cicmpy (MS SQL Server Table)
3. tbl_ink_bb (MS Access table)
4. tbl_ink_bb_status (MS Access table)
‘1’ contains information about our projects (Customer-ID, Project
description, start date, end date etc.)
‘2’ contains information about our business contacts (both customers and...updating spreadsheet
I am creating a spreadsheet that will most likely be used as a databas
type file. I need to create a link to it, so that multiple people ca
update it constantly. Does anyone know the basics of creating a .ln
Message posted from http://www.ExcelForum.com
a lnk file won't help you :-=
This is just a Windows 'Link' pointing to the specific file. (e.g.
created within the Windows Explorer). What you probably mean is within
Excel the option 'Tools- Share workbook'. Have a look in the Excel help
for 'shared workbooks'
Note: Excel is not a datab...Money 2006 wont update Citibank account....
Updating my Citibank account either takes for ages or just plain won't
work. Any suggestions? (WindowsXP|SP2 (IE 7.0 Beta 2 installed)
...cannot write to database query
I have created a query using a table and another query but cannot write into
it. I dont get an error message at all just a bleep. I have tried doing the
query using the 3 tables that I need but the same thing happens. Each
query/table concerned has an ID field which is linked to each other in the
That means that you have created a query that is not updatable. Beyond that
we would need to know more about your table structure and query.
As a guess you may need to use a Main form and subform(s) to control the
data entry in this situation.
In Access Help type th...Help with Outlook update
I'm using Windows 2000, and decided I would check to see if my Outlook
2003 needed any updates. It found Outlook Collaboration Data Objects
Update as a critical update. But when I clicked to install it, I get a
window that says:
This machine does not have the MS Outlook Email Security Update
installed. Please install the MS Outlook Email Security Update before
running the CD0 1.21 Email Security Update.
I've searched on the web and on the MS webiste/help for anything
about this and found nothing. I don't understand what I am supposed
to do...it's like it's telling me I n...Updating Call option prices
I sold some calls on Best Buy and entered the transaction
in Money 2003 with the BBYHV symbol. When Money updates
prices on the Internet, it doesn't update the price of
the calls. What do I need to do to update the prices?
In microsoft.public.money, Steve wrote:
>I sold some calls on Best Buy and entered the transaction
>in Money 2003 with the BBYHV symbol. When Money updates
>prices on the Internet, it doesn't update the price of
>the calls. What do I need to do to update the prices?