Query from 3 tables

I have three tables that I want to put in query. But this is what I
need:


Articles                Stores                  MinimumQTY
-----------                ----------
---------------------
ArticleID              StoreID                 ArticleID
ArticleName        StoreName            StoreID
ArticlePrice                                     MinQTY
                                                      OptimalQTY

What I would like is to have a query that shows all recors from
Articles, and for each article to show all Stores and for each StoreID
to show records in MinimumQTY. This seems like easy task but I want to
be able to enter quantities for each store directly in query. I mean
it should show every StoreID even if it doesnt have related record in
MinimumQTY.

It should be like this:

ArticleID
1001
         StoreID     MinQTY     OptimalQTY
              1               2                 5
              2
              3               2                 5
              4
              5
ArticleID
1002
         StoreID     MinQTY     OptimalQTY
              1
              2                4                10
              3
              4
              5

If I could have this I could simply enter MinQTY for ArticleID and
StoreID in query to be for example 5.


Any help ?


Thanks in advance.
0
exebat
12/8/2009 7:41:27 PM
access 16762 articles. 3 followers. Follow

3 Replies
641 Views

Similar Articles

[PageSpeed] 9

Exebat -

You are asking to do two types of queries in one - an update for records 
that are there, and an append for new records.  You can't do that in one 
query.   This approach might work for you:

If you really should have all articles at all stores, then run an append 
query to populate the MinimumQty table with all StoreID / ArticleID 
combinations that are not there.  Then you can run a query that will show you 
what you want on a form (use Datasheet Mode to make entry easiest).

If you do not want all articles at all stores, then you need to decide what 
the trade-off is of showing the possibilities.  You can also take the 
approach above, followed by a delete query that will remove all records with 
no MinQty and no OptimalQty.

-- 
Daryl S


"exebat" wrote:

> I have three tables that I want to put in query. But this is what I
> need:
> 
> 
> Articles                Stores                  MinimumQTY
> -----------                ----------
> ---------------------
> ArticleID              StoreID                 ArticleID
> ArticleName        StoreName            StoreID
> ArticlePrice                                     MinQTY
>                                                       OptimalQTY
> 
> What I would like is to have a query that shows all recors from
> Articles, and for each article to show all Stores and for each StoreID
> to show records in MinimumQTY. This seems like easy task but I want to
> be able to enter quantities for each store directly in query. I mean
> it should show every StoreID even if it doesnt have related record in
> MinimumQTY.
> 
> It should be like this:
> 
> ArticleID
> 1001
>          StoreID     MinQTY     OptimalQTY
>               1               2                 5
>               2
>               3               2                 5
>               4
>               5
> ArticleID
> 1002
>          StoreID     MinQTY     OptimalQTY
>               1
>               2                4                10
>               3
>               4
>               5
> 
> If I could have this I could simply enter MinQTY for ArticleID and
> StoreID in query to be for example 5.
> 
> 
> Any help ?
> 
> 
> Thanks in advance.
> .
> 
0
Utf
12/10/2009 6:19:02 PM
"Daryl S" <DarylS@discussions.microsoft.com> wrote in message 
news:4D2A7A07-A528-4EB1-815C-0DC2351BB692@microsoft.com...
> Exebat -
>
> You are asking to do two types of queries in one - an update for records
> that are there, and an append for new records.  You can't do that in one
> query.

Actually, you can.

See my November, 2003 "Access Answers" column in Pinnacle Publication's 
"Smart Access". You can download the column (and sample database) for free 
at http://www.accessmvp.com/DJSteele/SmartAccess.html

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



0
Douglas
12/10/2009 6:36:34 PM
Douglas - your code requires the 'new data', which in Exabat's case will 
require a cartesian product query, as there is no 'new data' table in this 
case.  So we are back to two queries in either case, though with your method 
you would only need to run one of them, the other would be needed as the 'new 
data' source.  In either case, if all the records are needed (i.e. all 
articles should be in all stores), then this is OK, otherwise there will be 
some cleanup to do.

-- 
Daryl S


"Douglas J. Steele" wrote:

> "Daryl S" <DarylS@discussions.microsoft.com> wrote in message 
> news:4D2A7A07-A528-4EB1-815C-0DC2351BB692@microsoft.com...
> > Exebat -
> >
> > You are asking to do two types of queries in one - an update for records
> > that are there, and an append for new records.  You can't do that in one
> > query.
> 
> Actually, you can.
> 
> See my November, 2003 "Access Answers" column in Pinnacle Publication's 
> "Smart Access". You can download the column (and sample database) for free 
> at http://www.accessmvp.com/DJSteele/SmartAccess.html
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> 
> .
> 
0
Utf
12/11/2009 3:11:02 PM
Reply:

Similar Artilces:

OT: demoralized tables
Kirk Kuykendall has a good overview on Common Table Expressions http://www.sqlservercentral.com/articles/CTE/62291/ but his conclusion contains a "new db term" (at least I had never seen it before)... "As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables. Like imported spread sheets." I assume it was on purpose and couldn't resist passing it on. We've all been there... gary I'm wondering if demoralized tables might be an undiscovered cause of file corruption. I would think that one table wit...

Bill Reminder #3
In the older versions of Money it used to have a reminder that shows up the upcoming or overdue bills. I couldn't found it on the 2005 Premium version. Is it there ? I setup a bill but it didn't remind me at all. I think you mean Money Express? It stopped working reliably in M2004 and has been removed from M2005. I guess the effort to make it work properly was out of proportion! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/...

data points #3
I have a scatter chart with the following info : sales price, square footage, and subdivision. With the x and y axis being sales price and square footage. There are some entries where i will have several sales in one subdivision and i would like all those points to show up in the same color. How do i get several points to show up in the same color instead of excel assigning each entry a new label? Include all the related points in the same data series, rather than as a different data series for each point. -- David Biddulph "affordablegreen" <affordablegreen@discussion...

9.0 Table Changes
Will be updating to V9, am looking for a list of the tables that changed and the documentation says it is on V9 cd 2, under the SDK But I don't see anything on the CD so do I have to install v9, and the SDK before I can get a list of the table names? -- Doug You need to install the SDK "Doug" wrote: > Will be updating to V9, am looking for a list of the tables that changed and > the documentation says it is on V9 cd 2, under the SDK > > But I don't see anything on the CD so do I have to install v9, and the SDK > before I can get a list of the tabl...

MS CRM 3.0 Customer Case Problem
Dear CRM Experts, I have a problem with MS CRM 3.0: I want to create a web based form in our company website that customers create their Cases (tickets) by it and we (I and my Tech. staff) response to them (Ticketing system). It seems for security reasons we shouldn’t allow CRM browsing outside of our Internal Network (Intranet). Please help me how can present solution for this problem. Best Regards, Mohsen Ahmadi You have a couple of options. There are third-party apps that will let you integrate CRM with your website, but with 3.0 you would probably need to purchase the External C...

Error while import BCM Database into CRM 3.0
I am trying to import BCM Database into CRM 3.0 When I run " Data Migration for Microsoft Outlook Business Contact Manager" wizard I get an unexpected error. Can someone help ? thanks Here is the Log: 8/21/2007 5:15:38 PM------>Transitioning to next screen. From: Welcome screen. To: PreconditionCheck screen. 8/21/2007 5:15:38 PM------>Connecting to Microsoft CRM Shamir_Systems_MSCRM 8/21/2007 5:15:39 PM------>Checking that Microsoft CRM Data Migration Pack is installed: Success 8/21/2007 5:15:39 PM------>Establishing connection to CDF database: Success 8/21/2007 5:1...

Delete 3 end row
Hi I want to delete three end row of any sheet. any sheet has different row number. Thank's for any help. your's Hi, This will clear the last 3 rows of the active sheet. Note I the on error line is to cope when there are less than 3 rows on the sheet Sub del_Last_3() Dim LastRow As Long On Error Resume Next LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row ActiveSheet.Rows(LastRow - 2 & ":" & LastRow).ClearContents End Sub -- Mike Whe...

Drop down menu #3
Hi, I want to create a drop down menu for my customer list. The drop down menu should have a auto search function. eg. If I type in "Mic", the drop down menu will bring me to the customer name close to "Mic". How can I do it? Can I update the customer list regularly. Pong This previous post by Debra Dalgleish would be of interest: "Although data validation doesn't support autocomplete, there's a sample file here that provides a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocom...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss�:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing tables in the CRM database and/or adding complete tables to the CRM database?? I am asking this from the point of view relating to other existing CRM products (saleslogix, frontrange - shh). From what I have played with so far from playing with my SBS2k/MSCRM install is I my need to have a MS developer skillset. Robb, Using the schema manager in the deployment Manager MMC snap-in, you can add fields to the existing tables. However, there is no way to add new tables. Matt "Robb D" <robbd@eproductscons...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

customizations import from 3.0 to 4.0
Is is possible to import customizations into a 4.0 system that were exported from a 3.0 install? You will need to upgrade 3.0 implementation to 4.0 by running MS CRM 4.0 setup. Please go through Microsoft Dynamics CRM 4.0 Installing Guide. You can download this guide from folowing URL http://www.microsoft.com/downloads/details.aspx?FamilyId=1CEB5E01-DE9F-48C0-8CE2-51633EBF4714&displaylang=en I don't think that there is any supported way through which you can import 3.0 Customization file to 4.0 implementation. Cheers, Rohit "Leo_B" wrote: > Is is possible to im...

Crystal Reports 9.2.3 and CRM
Hi, I'd like to know if CRM does work fine with the Crystal Reports Pro Version 9.2.3. I know the recommanded version is 9.2.2 but was just wondering if somebody successfully worked with 9.2.3/CRM Thanks Eric I have used a version line 9.2.4 so I would assume yes it will work. Only way to be sure is to try it. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Eric" <NOSPAMerich742001@yahoo.fr> wrote in message news:%23GB4wV9SFHA.1040@TK2MSFTNGP10.phx.gbl... > Hi, > > I'd like to know if CRM does work fine with the Crystal Reports Pro Ve...

Money Download #3
I am assuming with all the issues, Microsoft is not going to be making Money available for download today? Don't see why not, the download version isn't going to be any different to the one you buy in the shops! But do please think twice about using a download and rather get a version on media! What are you going to do when your hard disk dies taking your downloaded copy of Money with it?!! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsof...

CRM 3.0 Mobile for Cingular 8525 (HTC Hermes)
I was just looking at the minimum requirements for CRM 3.0 Mobile and I see only the Texas Instruments and Intel processors listed. The 8525 uses a Samsung 400MHz processor. Is there a plan in place to test and release on this platform? The Intel processors usually mean Xscale processors or ARM processors. The Samsung chip is almost certainly a repacked ARM processor so should work. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "captbackfire" <captbackfire@discussions.microsoft.com> wrote in message news:A818737C-44F3-4...

Slow acces CRM 3.0
Hi, We have a problem, our CRM goes slow. There are 10 active users using CRM with web browse, and sometimes Outlook client. Our server is a Compaq Proliant with 2 processor Xeon and 2 Gb of RAM. We installed CRM SBS, exchange, sql server in the same server. We accomplish all recommended requierements says by Microsoft but when I go to open an account it's delay about 10 seconds. Why?? thanks in advance I have recently come accross a client that forgot to turn of the tracing in CRM, and this was causing a lot of delays, and the type of wait time's that you describe. Could it...

OWA login problem #3
Initialy there was an error ASP 0115 Errors in IIS This what I 've done ; Click Start, point to Programs, click Windows NT 4.0 Option Pack, click Microsoft Internet Information Server, and then click Internet Service Manager. Expand Internet Information Server. Right-click the computer name, and then click Properties. In the Master Properties drop-down list box, click WWW Service, and then click Edit. Click the Home Directory tab, and then click Configuration. On the Process Options tab, click to clear the Enable Debug Exception Catching check box. Click OK to close the Application C...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

Pivot Table #46
Hello, I have pivot table based on data that identifies an item as "late" "late if not rec'd today" and "late if not shipped today". The totol lists the correct number from each category for a location but when I double click on that total I get a list of every entry for that location, not just the late etc. What am I doing wrong? When you double click on a total, does it generate a new worksheet? If yes, it is listing out all the data, which the total field is made up of. The other possibility is that you are unhiding a summary field. Govind. xgirl...

Corrupt / Missing Table
OK, so trying to diagnose a series of annoying problems, I've found that we are actually missing the ActivityBase table all together. I don't know how it happened, when it happened or why, but more importantly is that its gone, and we're adding data to the database faster than I can keep up. Any ideas on how to recreate the database? We can't afford to role back nor loose data in a reinstall... TIA! I would contact MBS support and open a support ticket. The $245 for the incident will be well worth it down thee road as you may have a lot of other problems as well. -- ...

Pivot table total decimal places rounding
The totals in my pivot tables have too many decimal places. I total -$1,025.31 and $1,023.87 and the result is $1.43999999999994 in the total cell of the pivot table but I need just $1.44 I have formatted the table as currency and using 97. Pivot tables can lose their formatting if the data is refreshed. Reapply the formatting, and make sure that 'Preserve formatting' is checked (under PivotTable>Table Options). AM wrote: > The totals in my pivot tables have too many decimal places. > I total -$1,025.31 and $1,023.87 and the result is > $1.43999999999994 in the to...