Linking Queries

Can anyone help?


I have a system where employees register property with us. They then report 
back to us when they sell the property.

Registered properties are held in one table and sold properties are in 
another, where the common field is the employee.

I want to create a query where it will show the employee and the details of 
the registerly and the sale.

I cant seem to get the details of the registry and the sale to assign to the 
correct employee.

Thanks


0
Utf
7/31/2007 1:56:05 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
629 Views

Similar Articles

[PageSpeed] 12

If you were limited to working with a spreadsheet, you might come up with a 
design that puts properties in one status on one spreadsheet, and properties 
in another status on another spreadsheet (although this wouldn't be 
necessary).

You won't get the benefit of Access' relationally-oriented features and 
functions if you insist on feeding it 'sheet data.

Why not have one table for registered properties?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"SGTPEP" <SGTPEP@discussions.microsoft.com> wrote in message 
news:190D2657-A127-4E07-8EC1-60E6B055B9D8@microsoft.com...
> Can anyone help?
>
>
> I have a system where employees register property with us. They then 
> report
> back to us when they sell the property.
>
> Registered properties are held in one table and sold properties are in
> another, where the common field is the employee.
>
> I want to create a query where it will show the employee and the details 
> of
> the registerly and the sale.
>
> I cant seem to get the details of the registry and the sale to assign to 
> the
> correct employee.
>
> Thanks
>
> 


0
Jeff
7/31/2007 2:41:40 PM
For starters, Having two different tables with the same info in most cases is 
not the very best design.  It creates more problems that it cures.  All you 
really need is a field in the table that shows whether the property is sold 
or not.

Here is your first example.
> I want to create a query where it will show the employee and the details of 
> the registerly and the sale.
Because you have two tables, you will need to create a Union query to 
retrieve data from both tables.

> I cant seem to get the details of the registry and the sale to assign to the 
> correct employee.
Can you provide more detail, This is not clear.
-- 
Dave Hargis, Microsoft Access MVP


"SGTPEP" wrote:

> Can anyone help?
> 
> 
> I have a system where employees register property with us. They then report 
> back to us when they sell the property.
> 
> Registered properties are held in one table and sold properties are in 
> another, where the common field is the employee.
> 
> I want to create a query where it will show the employee and the details of 
> the registerly and the sale.
> 
> I cant seem to get the details of the registry and the sale to assign to the 
> correct employee.
> 
> Thanks
> 
> 
0
Utf
7/31/2007 2:42:03 PM
To give you a better understanding of the project:

I am trying to create a system where we recieve 2 excel reports a month from 
30 offsite employees. 

The 2 reports are imported and reports are created.


Im essentially looking to create a league table for employees. i.e. A table 
with the employee id and how many properties they have registered and how 
many they have sold.

I therefore want to pull the ID from one table, the number of registed 
properties and whether it has been sold or not.
This project is at the design stage.
"Klatuu" wrote:

> For starters, Having two different tables with the same info in most cases is 
> not the very best design.  It creates more problems that it cures.  All you 
> really need is a field in the table that shows whether the property is sold 
> or not.
> 
> Here is your first example.
> > I want to create a query where it will show the employee and the details of 
> > the registerly and the sale.
> Because you have two tables, you will need to create a Union query to 
> retrieve data from both tables.
> 
> > I cant seem to get the details of the registry and the sale to assign to the 
> > correct employee.
> Can you provide more detail, This is not clear.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "SGTPEP" wrote:
> 
> > Can anyone help?
> > 
> > 
> > I have a system where employees register property with us. They then report 
> > back to us when they sell the property.
> > 
> > Registered properties are held in one table and sold properties are in 
> > another, where the common field is the employee.
> > 
> > I want to create a query where it will show the employee and the details of 
> > the registerly and the sale.
> > 
> > I cant seem to get the details of the registry and the sale to assign to the 
> > correct employee.
> > 
> > Thanks
> > 
> > 
0
Utf
7/31/2007 3:32:06 PM
Reply:

Similar Artilces:

Account not linked to budget
I have a checking, savings accounts (banking type), a credit card account (credit card type) and 2 cash accounts (cash type) all attached to my monthly budget. I also have 2 different Line of Credit accounts (liability type) that I can not seem to attach to my budget. If I write a check for something from one of the Line of Credit accounts, the payment and category is not recorded in the budget. When I edit my budget and choose "accounts included in the budget summary", the 2 line of credit accounts do not show up. Any ideas? Have I chosen the wrong account type for these...

Database query, spreadsheet size
I have a spreadsheet with several database queries from SQL Server. As queries are refreshed, spreadsheet size continues to grow. Even when I wipe out the queries, the file stays around 300K. Does anyone know where this extra data is sitting and how can I clear it out. Hi 300K is not that big but have a look at: http://www.contextures.com/xlfaqApp.html#Unused >-----Original Message----- >I have a spreadsheet with several database queries from >SQL Server. As queries are refreshed, spreadsheet size >continues to grow. Even when I wipe out the queries, the >file stay...

Linking Tasks
How do I constrain Task 3 to start and finish as follows: Tasks 2 and 3 start as soon as Task 1 is completed. Task 3 finishes 2 weeks after Task 2 is completed. I'm setting the predecessors for Task 3 as follow => 1,2FF+2 wks. Finish day is calculated correctly but the start date moves to match the default duration. Thanks in advance, Diana This is a varaiation on a Hammock task. The start is driven by 1FS and the finish is driven by 2FF+2wks. What would do is estblish an intermediate milestone between 2 & 3 called "Task 3 may finish" (or somethin...

Importing data to Excel using MS Query
I am importing data into Excel 97 using the data query functions and MS Query. I can set simple criteria, but I am also sure I could do more with this if only I knew how to. Could anyone recommend some (simple!) reference material that explains more about setting up SQL queries. TIA, John. -- John Reynolds This is a pretty good site for a basic MS Query tutorial. http://www.rdg.ac.uk/ITS/info/training/notes/excel/query/ For learning SQL...I always recommend: The Practical SQL Handbook: Using Structured Query Language It's not Microsoft specific, but it will teach you what you ...

Problems with small numbers in Query and in XL pivot table
Hi, I have some CSV data that I want to use in a Pivot Table: I'm accessing the data from XL through MS Query using the MS text driver. I have a field named 'Ratio' with significant digits up to the ninth decimal place. Ratio 0.000118019 0.0000012 0.0000014 0.0000028 0.000001488 But, ... in Query and in Excel, everything is showing up as zero. How can I get the correct number to display and calculate in the Pivot Table ? TIA Leon Works by me. No data loss. HTH -- AP "Leon Noel" <LeonNoel@LeonNoel.kz> a �crit dans le message de news:443a2551$0$21291$8fcfb9...

Query SQL server db with cell value parameter
I want to supply criteria value using a cell on the spreadsheet to contain a date. Can't seem to figure out how to do this. On the Connections - Properties - Definition dialog, "Parameters" is grayed out. ...

Link for downloads???
Everything seems to have changed on Microsofts pages and now it seems almost impossible to find anything. Does anyone have a link to the hotfix and service pack for RMS? I can't find it and do have a service plan.... It appears they have changed the pages to invalid links or the server(s) are down. The same problem happens on both customer and partner source. I sent an e-mail to report the issue. Maybe they will have it corrected tomorrow. "Pete" wrote: > Everything seems to have changed on Microsofts pages and now it seems almost > impossible to find anything. Do...

Excel links #6
I have a word document that I have a link to an excel worksheet with. I need to know how to make the Excel worksheet fit on the correct page width for a word doc. In Excel it fits on one page width. But when I link it, it doesn't fit the width. This is due for a client today I need help. Hi, Can't you select it and drag to resize?? jr >-----Original Message----- >I have a word document that I have a link to an excel >worksheet with. I need to know how to make the Excel >worksheet fit on the correct page width for a word doc. > >In Excel it fits on one p...

Outlook 2003 and opening link question
I'm wanting to change the way Outlook 2003 opens links/webpages - from opening them in Explorer to Firefox. Please guide me to where the option to change this is. Thanks in advance! There is no such setting in Outlook; set Firefox as your default browser. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Ben" <bswong@bigpond.net.au> wrote in message news:%xboj.9252$421.7874@news-serv...

Link Rows Between Worksheets
Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Click on link problems #2
When I click on a url link in email---a box opens saying "Open Link Browser". And also a browser window. How do I get rid of the smaller link browser? Read your reply on your post of the 17th? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Robin" <robinhood2@juno.com> wrote in message news:atmnk0pgs4m272iunkegt0rvq4ssjqisig@4ax.com... > When I click on a url link in email---a box opens saying "Open Link > Browser". A...

Ldap Query
I want to make a query where i can see the members of an administrative group or Distribution list. Is there someone who has such a query? administrative group would be based on something similar to (legacyexhangedn="/o=Microsoft/ou=First Administrative Group/cn=Recipients") dl would be based on (memberOf="DN of Group") This works fine as long as the group isn't the users primary group -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this...

Customize link not working in Reports
Money 2002: Version 10.0 When I open a report (any report) and then choose the Customize... link on the left side, I do not get the Customize Dialog box that I used to see. Money is basically unusable until I hit the ESC (Escape) key. Any ideas? If you are using Money 2005, go into Tools/Reports and see whether you are using Essential or Advanced Reports. My guess is that you need to switch from Essential to Advanced. This is new in Money 2005. "LongTime User" wrote: > Money 2002: Version 10.0 > > When I open a report (any report) and then choose the Customize....

Union Query
A union query I developed has suddenly stopped working. The syntax is: SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part 2]; Pulling up each query individually works fine. When i run the above union query I receive "Invalid use of Null". The number and order of the columns in each query are identical. Access 2003 here. Any ideas what could be the problem? I suspect it is the VBA code that you are using around the query, not the query, that stopped working. To confirm, does your query is executable from the query editor? If it does not, you also confirm t...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

Can't click through on links in e-mail
Ever since one of those "critical upgrades" we have been unable to click through to a web page from links in e-mail. This is a real pain for links that say "click here" rather than the URL. A blank, white Explorer session opens along with an error window that says "Locate Browser Link" with a cursor blinking in the "file name" field. Any help? TIA Bob Should have mentioned that we are running XP Home edition V5.1 and Outlook 2002 (not Outlook Express) SP-2. "Bob" <usenetsp_mtrap@hotmail.com> wrote in message news:#TpOgKQyDHA.246...

Select subform's query criteria from form's unbound cbo
I want users to be able to select a name from a form's unbound combo box and that selection to automatically become the criteria for the subform's underlying query, without the parameter box for the query popping up. How do I do this? I'm new to VBA and not an Access 2003 whiz either. TIA for your help! WorkerBee wrote: > I want users to be able to select a name from a form's unbound combo > box and that selection to automatically become the criteria for the > subform's underlying query, without the parameter box for the query > popping up. How do I do t...

Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the database toolbar in word. When I click “Insert Database” in word, and then browse for my query, the wizard doesn’t “see” any of the queries in which I changed a field name (ex. Dept: Department). Does anyone know a workaround for this? Right now, it’s only showing about half my queries which is a little odd. Is there a different way to insert my database? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...

Link to Worksheet
As soon as I open my worksheet it open 'open dialog box' looking for file. It has peicharts, trendcharts and data, I don't know which one i linked. I want to remove the link, how can I -- Message posted from http://www.ExcelForum.com Get a copy of Bill Manville's FindLink program: http://www.bmsltd.ie/MVP/Default.htm You'll be happy once again! "anupam <" wrote: > > As soon as I open my worksheet it open 'open dialog box' looking for a > file. It has peicharts, trendcharts and data, I don't know which one is > linked. I want to r...

how to link a .txt ( .dat) as a table by VB
Hi from Meudon (France) I want to link a .txt file as a table in my acces db. How to by VB : code and reference(s) (Acces 2003) Thanks by anticipation ...

How to import web link and avoid frame?
Does anyone have any suggestions on how to import web link into Excel and avoid frame on web page? When I browse following link, it look fine on IE, but when I insert this link into excel, it shows nothing, because of frame within HTML. http://www.hkab.org.hk/DisplayInterestSettlementRatesAction.do?Submit=Search&lang=en&year=2010&month=2&day=3 Does anyone have any suggestions on how to solve it within Excel? Thanks in advance for any suggestions Eric When I right click on the sheet>import to excel, I get. You can the record a macro to refresh. Rates ...

Relationship error in a query
Hello Everybody, I have four tables in a databse. Each database has at least one column containing prices of goods & services. However, these columns are not related in anyway (some other colums are). I am trying to summerize these price information in one query where I am trying to list the sum of prices from every table. When I try to do so I am getting an error part of which says: "One or more of the tables isn't related to the others. Click OK to edit system relationships." At least according to the error message (and this would have been my question anyway),...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

Resources
I understand there is a feature in Great Plains where I can pull up any screen in eEnterprise and see what column and table that text box is linked to? Is this true? What are the steps to get this information? ...

Query table using criteria from an Excel document
Hello, I have a Access table of names and addresses. There is a postcode (zipcode) text field. I also have an excel spreadsheet with a single column of postcodes; no duplicates. I need to get query to return each name and address in the table which has a postcode listed in the spreadsheet. Typing each postcode manually into the query seems clumsy. Any ideas? Add a link to the Excel data. Create a new query joining the linked Excel data and your Access table on the postcode field. Those that match will result. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...