Difficult One-to-Many Query

I have (2) back-end SQL Server tables that I've linked to a front-end ACCESS 
db.  One table contains Overall (summarized) data associated with a Product 
Order, and the other contains Specific data associated with the order.  Both 
tables have Customer Number as a Primary Key -- here's the structures of the 
(2) tables, as well as what I'm trying to do with the data:

Table 1  Overall Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
CompanyName
SalesRep
TotalSaleRevenue

Table  2  Specific Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
ProductName
ProductDescription
ProductPrice

There are (29) possible Products that a customer can select for each 
order -- the Sales Rep processes the Order via an EXCEL workbook, and after 
configuring the order, rolls the data up to SQL Server via a sSQL = "INSERT 
INTO statement -- this process works fine.  At some point, we'll want to do 
some analysis on Quarterly Sales, so I've linked the tables to a front-end 
ACCESS db for Reports.  Here's where the problem lies ....

There's a 1:29 ratio (one-to-many) relationship that needs to be established 
between the (2) tables -- I've created a query of both tables, and added 
Left-Join from the Specific Data to the Overall Data -- this gives me all 
data from both tables.  When I display this combined table on an ACCESS 
form, I need to be able to show all (29) possible Products that were ordered 
for each CustomerNumber -- since each Product ordered is in a seperate line 
(recordset), I'm not certain how to achieve this (getting all (29) on a form 
when I search for a particular CustomerNumber.

Many thanks in advance for any assistance on this one.

Shane

 


0
doctorjones_md
4/20/2007 5:56:35 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
545 Views

Similar Articles

[PageSpeed] 4

On Fri, 20 Apr 2007 12:56:35 -0500, "doctorjones_md"
<doctorjonesxxxxx_mdxxxxx@yahoo.com> wrote:

> I'm not certain how to achieve this (getting all (29) on a form 
>when I search for a particular CustomerNumber.

Simplest would be to use a Form for the "one" side table with a continuous
Subform for the "many", linked by CustomerNumber.

             John W. Vinson [MVP]
0
John
4/20/2007 6:59:03 PM
Thanks for the suggestion John -- I'll give it a shot!  :)
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:sa3i231bb265uskgila257buvv1l9gudp3@4ax.com...
> On Fri, 20 Apr 2007 12:56:35 -0500, "doctorjones_md"
> <doctorjonesxxxxx_mdxxxxx@yahoo.com> wrote:
>
>> I'm not certain how to achieve this (getting all (29) on a form
>>when I search for a particular CustomerNumber.
>
> Simplest would be to use a Form for the "one" side table with a continuous
> Subform for the "many", linked by CustomerNumber.
>
>             John W. Vinson [MVP] 


0
doctorjones_md
4/20/2007 7:32:59 PM
je voudrais mettre � jour ma boite d'envoi mais je n'arrive pas 
pourriez-vous m'aider
"doctorjones_md" <doctorjonesxxxxx_mdxxxxx@yahoo.com> a �crit dans le 
message de news:%23o3ZJU3gHH.A.4596@TK2MSFTNGP05.phx.gbl...
>I have (2) back-end SQL Server tables that I've linked to a front-end 
>ACCESS db.  One table contains Overall (summarized) data associated with a 
>Product Order, and the other contains Specific data associated with the 
>order.  Both tables have Customer Number as a Primary Key -- here's the 
>structures of the (2) tables, as well as what I'm trying to do with the 
>data:
>
> Table 1  Overall Data.
>
> (No Primary Key)
> FIELDS:
> IndexNumber
> CustomerNumber
> OrderStatus
> OrderDate
> CompanyName
> SalesRep
> TotalSaleRevenue
>
> Table  2  Specific Data.
>
> (No Primary Key)
> FIELDS:
> IndexNumber
> CustomerNumber
> OrderStatus
> OrderDate
> ProductName
> ProductDescription
> ProductPrice
>
> There are (29) possible Products that a customer can select for each 
> order -- the Sales Rep processes the Order via an EXCEL workbook, and 
> after configuring the order, rolls the data up to SQL Server via a sSQL = 
> "INSERT INTO statement -- this process works fine.  At some point, we'll 
> want to do some analysis on Quarterly Sales, so I've linked the tables to 
> a front-end ACCESS db for Reports.  Here's where the problem lies ....
>
> There's a 1:29 ratio (one-to-many) relationship that needs to be 
> established between the (2) tables -- I've created a query of both tables, 
> and added Left-Join from the Specific Data to the Overall Data -- this 
> gives me all data from both tables.  When I display this combined table on 
> an ACCESS form, I need to be able to show all (29) possible Products that 
> were ordered for each CustomerNumber -- since each Product ordered is in a 
> seperate line (recordset), I'm not certain how to achieve this (getting 
> all (29) on a form when I search for a particular CustomerNumber.
>
> Many thanks in advance for any assistance on this one.
>
> Shane
>
>
>
> 

0
ahmed
4/23/2007 4:41:40 PM
Reply:

Similar Artilces:

Too difficult!
I don't seem to be able to get an answer to this question - is it so difficult or am I in the wrong forum? How can I change format a column in all 30 or so sub- folders in my INBOX in one go i.e. without doing them individually. Thanks for any help. Glyn Create your own view in View-> Arrange By-> Current View-> Define Views... and apply it on first use of the folder -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Glyn" <anonymous@d...

IF and conditional sum, one result
Hello everybody. I am trying to do something with a spreadsheet and no sure how to work the formula. I have seven columns and seven rows. Th column starts at F and the rows start and 1. The formula I have i this: =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))) This is in cell K6. I would like this formula to also be able to loo at cells L6 and L7, and if L7 is larger than L6, subtract 1 from resul of the formula in K6. Can something like this be done? Thank you i advance for any help that ca...

can't get query to group
here is the sql. can someone help me determine why the query isn't grouping by GoupNumber? The GroupName is the same for the GroupNumber. ID is unique. SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName FROM Accounts GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID HAVING (((Accounts.ID) Like "01*")); Example ID GroupNumber GroupName 010101 01 red 010102 01 red 010201 02 blue 010301 03 ...

How can I reuse the same Name on more than one worksheet?
I have a multiple worksheets in my workbook. I want to name a cell on each workbook by the same name "Litres" - note: I do NOT want a 3D reference to all at once, but individual references. On the same spreadsheet somehow I've managed this before - I have a name "Business" that is context-specific on whichever sheet I happen to be on, but I can't remember how I created it! Looking at at in the 'Names' dialog box, it appears in the list as: Business June ....where 'June' is the current sheet name. Can anyone help me with c...

Difficult function: help needed.
In need a function which calculates an overall weighing factor based on person age and the years the person is working at a company. For each working year factor 1. For all working years while older then 41 an addtional factor 0.5. For all working years while older then 51 an additional factor 0.5. Thanks for any help. To be able to use this function easily in each case paste this command into a Visual Basic Project, Macro Module: Function Factor(Years) If Years > 51 Then Factor = 2 ElseIf Years > 41 Then Factor = 1.5 Else Factor = 1 End If End Function Therafter you wil...

POS All In One Computers
We are looking at replacing all of POS computers with all in one units. I am looking for recommondations from the community on who we should look at. We have already looked at the Point of Sale Terminals from Radiant Systems and the SurePOS from IBM. Does anyone have any other suggestions? Thanks, Stephen Moore Stephen: Do not, Do Not, DO NOT purchase Radiant terminals for use with RMS; they won't work properly due to proprietary design. Everyone has all-in-one units today. Units that are compatible with nearly all POS programs are available from PosiFlex, Pioneer, T...

Can not update data on a query based form
I cannot update data in a query based form. The properties are set for update but the form will not accept changed data. Post the SQL statement of the query. Regards Jeff Boyce Microsoft Office/Access MVP "Arbys4131" <Arbys4131@discussions.microsoft.com> wrote in message news:C11E1418-E38C-4C02-A7D4-C05BE43DBBA3@microsoft.com... >I cannot update data in a query based form. The properties are set for > update but the form will not accept changed data. Arbys4131 wrote: > I cannot update data in a query based form. The properties are set > for update but t...

Button to move from one chart to another
I have compiled data for 5 regions which includes 17 cities. I have create a chart for each city and one for each region. Is there a way to create buttons or links on the Regional chart that would automatically take the user to that selected Cities chart. This would eliminate the need to scroll at the bottom to get to the correct chart. Bryan, you can add macros to your workbook that take users to the desired location, and assign buttons for their use. Such will quickly become a pain to maintain. An alternate approach is to range name the cells beside the charts with the city nam...

Outlook does not recognize one or more names
When trying to send mail with outlook this is what my response is, "oultlook does not recognize one or more names. I can send mail when using my verizon mail program but not with outlook. Any ideas? have you verified that both you imcomming and outgoing mail servers are set correctly? I believe that verizon has several outgoing servers. You may want to contact their technical support to get the correct configuration for your account. You also did not note if you are using a router or firewall program on your computer. This can also create additional issues with your configurat...

Using JScript Web Service Query On ActivityParty
Hi, I'm getting errors trying to use a JScript web service query on the ActivtyParty entity. The response XML says: "The Retrieve Multiple method does not support entities of type '<activityparty>' " Any idea why this is? How can I query on this Table?? I need to retrieve an Account Name from a Service Activity, and the "customer" data field cannot be queried -- MD ...

Change date by one year
I have a list of birthdays formulated 8/12/05 I want to change all the dates to next year i.e. 8/12/06 oldjay You could use a formula: =date(year(a1)+1,month(a1),day(a1)) Oldjay wrote: > > I have a list of birthdays formulated 8/12/05 > I want to change all the dates to next year i.e. 8/12/06 > > oldjay -- Dave Peterson Or, for a "quick and dirty" solution Ctrl+h to bring up Find and Replace Find What /2005 Replace with /2006 Replace all Regards Roger Govier Dave Peterson wrote: > You could use a formula: > > =date(year(a1)+1,month(a1),day(...

how can i append data to a sheet or book from another one
we can append data to a file from anther file in dbase. is it possible in excel??????? Abdulla, if you are looking for a VBA solution check out the below links by Ron de Bruin.. http://www.rondebruin.nl/copy1.htm http://www.rondebruin.nl/copy2.htm http://www.rondebruin.nl/copy3.htm -- Jacob "Abdulla" wrote: > we can append data to a file from anther file in dbase. is it possible in > excel??????? ...

remove duplicates from one cell at a time
I am trying to get rid of dups from a table that I imported. ID Data 1 string1, string2, string1, string4 2 string2, string6, string2, string1 I want it to look like ID Data 1 string1, string2, string4 2 string2, string6, string1 How would I go about doing this. Thanks Are you saying that you want to end up with a field that contains multiple facts (e.g. "string1, string2, string4")? This is not a good database design. You may be receiving data structured like this, but if you want to make the best use of Access' relationally-oriented features and functi...

how do i use multiple conditional formats in one cell?
I need to make difrent "words" in one cell make number values in a difrent cell tyson CF will allow up to 4 formats per cell, including the default format. However, CF will not place number values in cells. Can format only as in Fonts, Borders and Patterns. What would you like to have done? Perhaps there is another method. A worksheet function such as one of the LOOKUP functions or MATCH. Gord Dibben Excel MVP On Fri, 21 Jan 2005 14:55:03 -0800, "tysonstone" <tysonstone@discussions.microsoft.com> wrote: >I need to make difrent "words" in one ...

Get object from one CDialog to another
I have a dialog MFC application, and I have it so that another modal dialog opens before the main dialog. The first dialog is for logging into a "Campaign". As soon as the first dialog closes the main one opens. How do I get a Campaign object from the first modal dialog into the second (main dialog)? You could have Campaign* members in both your dialog classes and do something like this :- dlg1.DoModal(); dlg2.m_pCampaign = dlg1.m_pCampaign; dlg2.DoModal(); -- Regards, Nish [VC++ MVP] "Si" <si@hotmail.com> wrote in message news:JkR9f.25724$6i4.7472@news...

Can Pivot Table views of a query be saved as a favorite?
....if so, how? Yes. Have the query properties sheet visible (right click on an empty spot on the top half, if not, or use the toolbar): the second property is Default View. Specify the one you wish to get. Hoping it may help, Vanderghast, Access MVP <soarathorn@gmail.com> wrote in message news:1177084713.529654.35070@l77g2000hsb.googlegroups.com... > ...if so, how? > ...

Difficult but do-able?
In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(S...

Query Filter using Not In
I want to filter a query to not show a list of values. I know to use Not Iin('something', 'something'), but what if I want to use a wildcard? How do I do a not in query using a wildcard? Thanks! Use a temp table, one field, CompareTo, to hold all the values, in different rows, one value you want to compare to, per row. Without wild card, use an equality: SELECT whatever FROM table1 LEFT JOIN tempTable ON table1.fieldName = tempTable.CompareTo WHERE tempTable.CompareTo IS NULL that is, indeed, something similar that the query wizard about finding unmatched reco...

Resolver One: New (and different!) Alternative to Excel
Hello all, I thought you might be interested to hear about a new spreadsheet program that is a new alternative to Excel. Although it uses the familiar interface to enter data and formulae, the underlying model is very different. Data and formulae are turned into code, making it much easier to use a proper programming model within spreadsheets. Spreadsheets are programmable with IronPython, meaning that you can use Python and .NET libraries within them (including putting arbitrary objects in the grid!). http://www.resolversystems.com/ Resolver One is free to use for personal use, and the no...

how can I combine two serials into one
Usually the serials are created automatically. But I want to combine two serials into one. For example, there are two serials "--------" and "+". I want combine these two into one such as "------+------". Is it possible? Hi not quite sure but maybe you're looking for =A1 & B1 where A1 and B1 store your two serials -- Regards Frank Kabel Frankfurt, Germany ye wrote: > Usually the serials are created automatically. But I want > to combine two serials into one. For example, there are > two serials "--------" and "+". I w...

Submit Button Works on one page only
I've was able to get the submit button working on one of my pages but on another page I get the The requested URL /index_files/--WEBBOT-SELF-- was not found on this server. when I hit the submit button. I'm spent in getting the first submit button to work. Please advise on this one. Thanks Perhaps the information in this article will help: "Publisher web publication forms 101": http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80564.aspx In the future I suggest that your use the subgroup microsoft.publisher.public.webdesign for web design questions. DavidF &qu...

Filtered form not using query
I have a form that has several subforms on it seperated by the tabs object. Most of the subforms have their data attribute set to SQL, where the data is ordered. One of the subforms has it's data attribute set to a query, where the data is ordered. When I look at the form with it's subform with a filter applied, called from code, the subform with the data set by query does not order it's results. The other subforms are fine! I'm going to try and duplicate this problem, but wondered if anyone had ever heard of such a thing ? Rohan. -- Message posted via AccessMonster.com...

Funky Query Question
I'm building a new query that uses 2 inputs; a query and a table. The input query has 3 fields (a,b,c) that combine to be a unique identifier for each record. The input table has the same 3 fields (a,b,c) that make a unique identifier for each record. I'm trying to match records from each input source using all 3 keys (a,b,c), incldung a field from the table in the new record. But my new query output only includes the first match of the unique identifier (a,b,c), and doesn't pick up the rest of the matches (a,b,d - b,c,f - c,d,h - etc.) I've tried various combination...

Multiple Entities on one Form
Hi, wonder if anyone can help. We are busy implementing CRM Titan. I want to know if it is possible to have a form that contains Accounts, Contacts and Activities, so if a select an Account I will see the list of all contacts and all Activities related to the Account on the same form. If I then select the Contact i want to only see activities related to that specific contact Many thanks Jason You can use Iframe and display related entities, but as you are looking parent child on single ms crm , you have to do custom development and achieve that. -- Regards, Imran MS CRM Certified P...

Qty Avail=2, Only One Serial #
Have a strange one. I have a serialized item showing 2 in stock (correct), but it's only showing one serial number. Nothing's allocated (nothing unposted) and if I do a Serial/Lot Trace, it's showing the missing serial number should still should be in stock. But when I do a Serial Number Enquiry, it only shows one serial number, although below it clearly states that both Qty Avail and Qty On Hand is "2". If I try to invoice 2 out, I have to enter in the serial for the second one, which of course would leave me with a qty of 1 after I invoice 2 out. I've run...