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.
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.
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three.
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database Dim personID As Integer Dim orgID As Integer Dim roleID As Integer Dim rsPerson As Recordset Dim rsOrg As Recordset Dim rsRole As Recordset Dim rsRel As Recordset
[code]...
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
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 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.)
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
Since the ClassId can return multiple records the output would be comma deliminated and only return 1 record per UserId. Is this possible within an Access query? Also, if so, how would you construct the query that had another additional table with a one to many relationship?
Sorry for posting yet another thread on many-to-many based queries, but mine aren't working and I need some pointers. I have downloaded and studied Pat's example of the many-to-many DB, and mine now looks fairly similar. Relationships have been properly defined, it's just that my forms dont seem to be behaving how I'd like them to.
These tables have been linked correctly and show the correct relationships (Papers and Authors are linked with one to many to the Join table)
I have a main form: Papers, data source 'qPapers'. In this I have a subform: Authors, data source 'qAuthors'. These queries are set up in the same way as Pat's, i.e., Authors uses the join fields 'AuthorNum' and 'PaperNum'.
However, when I open Papers form the Authors Subform displays all records and not just those linked to this particular paper. So in this way it doesn't function in the way that Pat's does.
Please help. I've studied Pat's closely and can't work out what I'm doing wrong. Many thanks
Trying to put together a query that has all my data. However when I get to the this table the query no longer works, has just the titles. When I add tblCallCredit the query stops working. If I remove that table I get the data that the query is supposed to fetch.
I am not sure if its a relationship issue or something else which I dont know about.
I have an existing Members Database Table fully populated with over 1,000 companies detailed. Within the database each company has a nominated person, normally the M.D. who has access rights to the member’s area of a website. We would now like to give the companies’ employee’s access as well. I would like to create a Parent / Child relationship between the two. I have a yes/no check box with the main table which signifies whether or not the company is still in membership. If the company was to cease membership by un-checking the box, access is denied to the employer and the employees. The employees table would only need to consist of First Name, Last Name, email address and password, and I presume a ID field to link the relationship.
Have I got that right or is there a better way?
I must admit, I'm confused as to the differences between Queries and Relationships, is that not the same thing?
If I have two tables say A and B with a one to many relationship and if there is no linked record in table B for a record in table A, if you run the query, that particular record does not show up at all.
Is there any way that this record can be shown in the query with no corresponding records in table B in the query (since there is no corresponding record)?
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables... 1) Retailers-RetailerID, RetailerName, address etc. (500 entries) 2) Distributors- DistributorID, DistName (10 entries) 3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table: RetailerID DistributorID 1 1 1 3 2 1 2 6 3 4 3 5 4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID WHERE Uses.DistributorID = 1 GROUP BY Uses.RetailerID, Retailers.Address HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way. Thanks in advance!
They are related with a one-to-many relationship. The related field is "InvoiceId"
I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.
I want to be able to edit the fields from tblInvoice in the query.
If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!
I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.
INSERT INTO Invoices ( InvoiceDate, AcctNum ) SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));
Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
