so, I have created a working SQL statement that summarises data from a bunch of different tables (it's a stock monitoring application so it all has to do with levels of stock, numbers of parts processed etc...):
SELECT tblPPIn.BatchID,
tblPartDescriptions.DrawingNumber AS [Drawing Number],
tblOrder.IssueNumber AS [Issue Number],
tblPartDescriptions.Description AS [Description],
tblPPIn.Qty AS [Total Booked In],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Qty Processed],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)) AS [Total Good Parts Taken],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)) AS [Total Uninspected Parts Taken],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Passes],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;))) AS [Total Fails],
[Total Booked In]-[Total Qty Processed]-[Total Uninspected Parts Taken] AS [Total Unprocessed Parts Remaining],
[Total Passes]-[Total Good Parts Taken] AS [Total Good Parts Remaining],
[Total Passes]/[Total Qty Processed] AS [Overall Yield]
FROM tblPPIn INNER JOIN (tblPartDescriptions INNER JOIN tblOrder ON tblPartDescriptions.DrawingNumber = tblOrder.DrawingNumber) ON tblPPIn.BatchID = tblOrder.BatchID
ORDER BY tblPPIn.BatchID ASC;
don't worry about the details, it works as it is above...
now I only want to display data for batches where there are some parts left, so I've tried adding the following WHERE clause:
WHERE ([Total Good Parts Remaining] + [Total Unprocessed Parts Remaining]) <> 0
should work fine, right?
nope, we get the (all too familiar) 'Enter parameter value: Total Good Parts Remaining' (and the same for 'Total Unprocessed Parts Remaining'), despite the fact I have used these aliases in the previous SQL with no problem...
Is there any way around this or do I have to create a big-ass WHERE statement complete with the subqueries I've already used previously?
This is seriously annoying me today... any help would be massively appreciated!
I am trying to use one Access 97 mdb file to attach to two different SQL servers depending on the situation, one is production the other is a backup server. (The application requires an Access97 file format so I can't change that) I have a DNS Alias for the server name and I put that in the ODBC under Server Name. When I have the Alias changed from Production to Backup I want the Access file to see the data on the backup box.
It appears that MSAccess stores the Server name when the link is made in the MSysObject.Connect field and it does not update that even, if the ODBC is changed. I have one test connection that seems to working because I created it using the ODBC after the Alias was added. The string it puts in the connect field has no reference to the server name or address similar to what is below.
So I think I need to recreate a new file using the Alias in the ODBC so the specific address is not in the connection string. What do you think? Has anyone used an Alias name like I am trying to switch between like databases on two different servers? It seems weird that in the first example above there is not reference to the alias name so maybe this forces it to look it up.
Does anyone know (and would be willing to share) how one can order the results of a Union query in Access by something other than the returned values?
I have a simple Union query that would work perfectly - if I could get the thing to order the results in a particular order everytime:mad:. The query returns counts (all from one table) of separate select statements that each meet certain conditions (e.g. having state=Nevada, having state=California, etc.) as records - but since Union queries in Access order results in ascending or descending order by returned value, it puts my records in a different order every time the record values change. I need to have them ordered the same way (in the same order as they appear in the Union query, if possible) everytime so that I can return those values to specific cells in MS Excel. Any suggestions would be much appreciated!
I have a subform on my main form which I can access properties etc without error, but when I try to set the sourceobject property I get an error saying it can find the object '~sq_cfrmAction~sq_csubPlanetItems'
Hello! I have a problem regarding using usernames and passwords to restrict access to certain tables/forms/reports etc.... I have read around the forum and various other websites but cant seem to get much relating to exactly what i am wanting to do.
I think i understand the way the access workgroup security works. But does this not just restrict users being able to view the database as read-only or with other certain rights like that? So, firstly would i be able to use this to restrict the database the way i want?
Or is there a way to create a table with names and passwords and create a form at the beginning where the user inputs their details and then according to the details gives/restricts access to parts of the database? Ideally i'd like the startup form to direct them to a different menu according to the user which gives them only the options they are allowed?
Seems a bit of a mouthfull and i sound like a complete newb but i been trying to figure this out all day!! Thanks
Hi all, I have been using Excel data in Access either by importing the sheet as a table or directly linking to the sheet with no problems in the past but today I cannot seem to get the data to import in the right way.
The column on the Excel sheet is mainly populated by numbers but some of the values have letters in them. When I discovered this I formatted the column as text and tried to directly link to the sheet but the datatype kept insisting it is number. I then decided to import the sheet as a table and although it comes in as Text instead of seeing the value A1030573 in comes in as 7.1e+009 and this goes for the values that are purely numbers.
Is this a known bug, does anyone have a solution to to this ?
Here's a weird problem I've had develop in the past couple months, before I upgraded to 2007, and now since I upgraded to 2007.
I've had the same Access database since 1997, and just recently upgraded it to Access2007, but as I said the problem occurred before and after the changeover.
I have two servers at the office. Server #1 (where the database is located) has attached to it is a dot matrix printer, used for printing our 4 part invoices/packing lists. I have another server (#2) which has a laser printer attached to it. The laser printer on server #2 is the default printer for all computers in the office. I have a lot of reports in Access where the printer to be used is the default printer. I have a single report in Access where I specified in the page design and layout to use a specific printer - the dot matrix on server #1.
Both servers are always on. Both printers are always on. I can go for weeks and weeks with no issues in printing the invoices. Then suddenly I'll get a
"This document was previously formatted for the printer (printer name) on (server #1), but that printer isn't available. Do you want use the default printer (\server#2laser printer) ? OK CANCEL SETUP... HELP
It worked fine for 9+ years, and now I am getting this error. No new computers in the last 2 years. No new printer installs in the last 2 years.
I've gone through, uninstalled the printer, reinstalled the printer, both on the server and remote machines, and now I can't even get the report to "remember" that I want it to always print to the dot printer.
I have several FE's each saving in their own BE. They are all the same but for different areas of our business. Each has around 20-30 users. I recently introduced another form into the FE that has linked tables to another different BE in another folder. Each FE out there with its own BE now has this form and all of the new forms save to the same (one) BE. Ok, got it?
Now, sure its possible to have them all together and I would anticipate someone suggesting this. Its split up to keep sizes down and performance up among other reasons.
In any event, here I am. All of the new form introductions went smoothly without a hitch. The last one however is causing trouble. On submit an e-mail is sent, the user is asked if they want to submit another form, and if yes a new record is created. If no the form is closed.
E-mails are always sent, no problem. The trouble is saving the record. All except one of the different FE's I have out there in different folders save the record into the table just fine. There is one though that does not do this. The mail is sent and the program behaves normally with no errors but the record is not saved. This does not happen to all users of this FE either. There are some users that work fine and the record is saved in the BE yet other users do not save the record. You can see the .ldb appear and disappear. Our IT dept has been through the permissions time and time again and tell me they are ok. The FE and main BE are in one folder while the BE that the new form talks to is in its own folder. Remember, this works fine for several other instances, its just the one that doesn’t save the record.
I did have a chance to see the permissions the other day and it seems a mess to me. They have users in individually, as they were set up initially and the same users are also in there in a group and maybe 2 groups. The permissions look a mess to me… they really do…. but that doesn’t necessarily mean they would not work and can be blamed. I have no control over this part and it drives me nuts!
What I have done...... copied a working FE from another area into the troubled folder. No good. I have removed the new form along with its linked tables and the record is saved. Put the form and linked tables back in and it stops saving again. The code compiles just fine. I have done compact and repairs.
Any thoughts or ideas out there??? If you need more info ask. I understand what I wrote but you might not LOL
I'm sure you've all seen this before - but, it's killing me. It's for a university project which is being demonstrated soon, and I can't get it working consistently!
When pages are loaded, I get the error -
Provider Error - 80004005, Unspecified Error /db/update_select.asp line 33 (line 33 is the connection to the database)
or
Internal Server Error 500 - Page cannot be displayed
And sometimes I get one about "Jet" "Threads", and ODBC stuff.
I'm guessing it's a driver problem, but I have no idea where to go from here! I'm running Windows 2000 Adv. Server, IIS 4 (I think) and my db is access 97 :O (I would have put a newer version on, but the CD-Rom is so old on the PC it won't take 700MB CDs! Argh.)
I have a pretty standard relationship set up, with the following tables:
Customers: A row/Cust ID for each customer WorkOrders: each customer can have multiple work orders (linked to Customers by CustID, individual ID is WOID) Jobs: each work order can have multiple job records attached (linked to WorkOrders by WOID, individual ID is JobID).
As far as I can tell, they're fine and all other forms etc work, updating no problem, referrential identity is enforced etc...
Problems: When I go to make a "job allocation" query for subsequent form, I select: Customers, Work Orders and Jobs table (I then filter by location, but this problem remains unfiltered also). They link up fine. The query isn't nonsensical either - it lets me add new data. BUT instead of showing all the customers and work orders for the location due for the location, it will ONLY display the records that have details in the Jobs table (the lowest in the relationship chain). Basically, If the jobs sections are empty or "unattempted", then NO details AT ALL will show up in the query. Which is a problem, because I want to see ALL the "unattempted" jobs in the area to allocate them, including customer data and work order numbers. I also need to have the jobs table present, so I can allocate a job date, a contractor etc.
I'm not sure what to do. As far as I can see, the set up I have IS very standard tables/relationships-wise. I have to work it out, otherwise I'm going to have to revert to some kind of 'super-table' (like in our old system) - which I am at loathe to do - I don't want to merge Work Orders and Jobs because that means a lot more typing for me.
Would cascade update in the relationships do anything? (it is checked).
I've been away from Access for awhile and I'm just drawing a blank on this one. Any help greatly appreciated. Thank you.
So, basically my database has quite a number of lookup fields in tables as there is much repeated data, each one references to a seperate table with an autonumber field (ID) and a text field (Value)
Currently I have column one (ID) as the bound column in such cases, as this is what the guy who started me on access said to do, and it's fine until I try and export any query with such a field to Excel.
I have found, through experimentation, that setting the 2nd column (Value) as the bound column allows me to export the values rather than numbers, but I don't know if this is a sensible thing to do? (ie, I don't know what other unexpected effects this might have)
Any guidance here would be very welcome, thanks, Bogzla
I've attached my db in the hope someone can help my head scratching.
I have got a Sales summary table with several other related table. Most notably, a table with the items in the sale and one with the costs. Because each sale might contain many different items and many different costs, I thought seperate tables were the way to go.
All I'm trying to do is make another query that gets the total sales (That's adding each line item * quantity) - (Each cost line item*it's quantity) and then finally the margin made on the deal.
However, if there are no costs or sales involved in the sale the query ignores it. (Sounds odd, but some transactions might be cost free, and some may not involve any revenue - so I have to bear it in mind).
I tried the Nz function, but It's either not what I need or I'm doing it wrong.
