This is a general question.
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)?
Thanks.
Two tables, Contacts and Jobs
On the Jobs I need to select the ID and show the Last Name, but only enter the ID. This has been done okay.
How do I get the Last Name to auto fill in from the selection of the ID number?
Hi all,
I have two tables: ImageSoft and Software.
All items of software which have a type SA (Standard Application) must have an entry in ImageSoft under every Image Code.
When I add a new software title of type SA, it won't be included in ImageSoft for every Image Code and as I have a lot Image Codes I'd like to automate the addition of these new software items to the ImageSoft Table.
If I do a query with three fields: ImageSoft!ImageCode, Software!SoftwareCode and Software!Type, where Type was the criteria of "SA", the query returns the results of all the items that are already in ImageSoft and not those that aren't. Is there a way to return all those that are not in ImageSoft and not return those that are? I can the use these results to append them to the ImageSoft table and therefore ensure that all type SA software items are matched with each Image Code in ImageSoft.
Cheers,
Matt
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
Table: User
Column(s): UserId
Table: Classes
Column(s): UserId, ClassId
User -> Classes is a one to many relationship.
In Access is it possible to create a query that outputs 1 record per UserId so that the output would look like:
UserId ClassId
102 2, 45, 6, 8
103 1, 44, 33
111 34, 2, 32
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?
Any assistance is appreciated!
Best, Fezzik
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.
Tables:
Papers
------
PaperID (key)
Title
Year
etc......
Authors
-------
AuthorID (key)
AuthorName
etc........
Join (for M-2-M)
---------
AuthorNum
PaperNum
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
Hello,
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.
Any clues?
Attached Image.
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?
Regards
Alan
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 have two tables which I pull records from. One table has a list of sections. The other table is a list of chapters.
One chapter has many sections.
I have the sections table populated, and want to show all of the sections, even if they are not currently assigned to a chapter.
Currently only the sections which have a chapter show up in my query.
I know this is really simple to do ..... I'm just drawing a blank on it!
Any help would be appreciated.
Gordon
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!
I have the following tables:
tblInvoice
tblInvoicePrePayments
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 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?
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.
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.
I have built a contacts manager based on an Outlook export (I know I can link the two programs, but I wanted to customize the contacts a bit further). I built the database in a way that phone numbers, addresses, etc. allow for multiple entries for each person under the same column using a foreign key. The only difference is a field called "Type" which designates the entry for Home, Work, etc. The issue I ran into is when I queried the data I get multiple entries for the same record. As you are all likely aware, Access repeats the data in a query for each different case. For example if Bob Jones has a personal and work phone number, Access will show the results in a query like this:
1. Bob Jones 555-555-5555 Personal
1. Bob Jones 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
If I kept the data in Access it wouldn't necessarily be an issue. However, I want to be able to export the data in an excel file. How do I get Excel to return the data so it looks like this:
1. Bob Jones 555-555-5555 Personal 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
any help would be appreciated
thanks
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
View 5 Replies View RelatedHi all :o
I am working on a query but can manage to get it to do what I want.
I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
Thanks
Jackie
Hello all,
I have a table called tblODF and within that table, I have these fields:
- ODFNumber
- ODFScanDate
- Status (Combo box: Pending, Complete, Licensing)
- LastFollowup
In my query, I have the same fields, except I added a calculated field.
I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
Is this possible?
Thanks
I've just started using Access 2007, but I've been an Access 2003 user for some years and have a couple of databases, both of which I've now brought over to 2007.
Something I've noticed and is probably very simple. In 2007, after I entered information in a table via one of my forms, I queried on that information but it wasn't included in the results. But everything else - from 2003 - was. I then went thru each field in the underlying table to "match" fields there were filled in for a 2003 record and now the new entry does appear in the query results.
This hadn't happened before in 2003 - it didn't matter if all or even particular fields were filled in. But now it needs for at least some particular fields to be filled in. Don't know if it's something different in 2007, or probably coincidental. I haven't changed anything in the table, query or entry form.
It's not a crisis (now that I see my results), but I'm wondering what is going on - now, that apparently wasn't occurring before?
Thanks!
I hope I can explain this clearly. I'd be really grateful if anyone could help.
I need to set up some sort of contacts database at work.
I need (I think, after having a little think about it) one table, filled with people, (their names, contact details ect) and basically various queries to pull up people in certain groups. Say for instance, people who attend meeting a, b, c, (in a simplised, condensed version of the truth)
I did think about doing this with various tables actually. A contacts table, a meetings table, blah blah, but anyway. (Any advice on this incidentally would be great).
My main problem though. We often have to email all the people who attend say meeting a. We have contacts on our email systems, but they're different from person to person, and as the company is updating email systems, we can't even send contacts lists to everyone, as they're not compatible.
What I'd love is to be able to call up a list of people attending meeting a from my database and email each of those people (their contact details would include email address of course)
Does that make sense?
Is there any way of doing this?
I'd be really happy if anyone knew...