Go Back   Access World Forums > Search Forums

 
Showing results 1 to 25 of 128
Search took 0.03 seconds.
Search: Posts Made By: sumdumgai
Forum: Queries 03-19-2019, 06:15 PM
Replies: 8
Views: 94
Posted By The_Doc_Man
Re: Need query to group sums where two fields match

Speed-wise, what I posted should be fast. If you are worried, write a macro to execute TWO action queries... one to erase the QTY field for all records in your T1, the other to do the summation as I...
Forum: Queries 03-19-2019, 06:08 PM
Replies: 8
Views: 94
Posted By June7
Re: Need query to group sums where two fields match

Yes, there are always exceptions to a 'rule'. See if this helps http://allenbrowne.com/AppInventory.html
Forum: Queries 03-19-2019, 02:28 PM
Replies: 8
Views: 94
Posted By June7
Re: Need query to group sums where two fields match

I question the need for this. Why do you want to calculate and save aggregate data?
Should save raw data and calculate aggregate data when needed, not save it.
Forum: Queries 03-19-2019, 02:12 PM
Replies: 8
Views: 94
Posted By The_Doc_Man
Re: Need query to group sums where two fields match

Something similar to:


UPDATE T1 INNER JOIN T2 ON ( T1.PID = T2.PID ) AND ( T1.SID = T2.SID )
SET T1.QTY = T1.QTY + T2.QTY ;


This assumes you have already created T1 with the combinations...
Forum: Queries 03-19-2019, 01:34 PM
Replies: 16
Views: 169
Posted By June7
Re: Query to update table from a Select query

I was responding to The_Doc_Man.

Go with whatever works. Just pointing out that what I provided also works and it is one query. See post 4. As stated, remove the Src field if you don't want...
Forum: Queries 03-18-2019, 01:45 PM
Replies: 16
Views: 169
Posted By June7
Re: Query to update table from a Select query

Okay, need to use an alias name for the subquery.

SELECT F1, F2 INTO myTb FROM
(SELECT F1, F2, "T1" AS Src FROM T1
UNION
SELECT F1, F2, "T2" FROM T2
UNION
SELECT F1, F2, "T3" FROM T3
UNION ...
Forum: Queries 03-18-2019, 01:15 PM
Replies: 16
Views: 169
Posted By June7
Re: Query to update table from a Select query

SELECT INTO creates a table, not updates or inserts existing.

Remove the as derived from each SELECT (missing a space in 2 of them).

Also have closing paren in wrong place. And must end the SQL...
Forum: Queries 03-17-2019, 03:40 PM
Replies: 8
Views: 122
Posted By CJ_London
Re: Update multiple fields in one table from multiple tables

This comment doesn't really fit your requirement as described but one other thought - is the data you receive normalised? If not you may find you get better capacity by moving the data to normalised...
Forum: Queries 03-17-2019, 07:55 AM
Replies: 8
Views: 122
Posted By The_Doc_Man
Re: Update multiple fields in one table from multiple tables

Your problem MIGHT be worsened by the fact that it isn't the table size that is the limit. It is the size of the recordset that cannot exceed 1 GB. So a query is also bound in terms of its size. ...
Forum: Queries 03-17-2019, 07:43 AM
Replies: 8
Views: 122
Posted By theDBguy
Re: Update multiple fields in one table from multiple tables

So, tab1 and tab2 are linked tables and grptab is a local table, correct?
Forum: Queries 03-17-2019, 07:07 AM
Replies: 8
Views: 122
Posted By theDBguy
Re: Update multiple fields in one table from multiple tables

Hi. This should be possible but why do you have multiple tables with similar structures? It doesn’t sound like your table structure is properly normalized.
Forum: Queries 03-16-2019, 08:42 AM
Replies: 1
Views: 81
Posted By June7
Forum: Queries 03-14-2019, 06:16 AM
Replies: 7
Views: 90
Posted By plog
Re: Need help with queries that span tables.

Follow MajP's example when building your UNION. The thing to remember is that the UNION should simply bring all your data together into one query object.

--Don't apply criteria to it
--Don't try...
Forum: Queries 03-14-2019, 06:12 AM
Replies: 7
Views: 90
Posted By MajP
Re: Need help with queries that span tables.

https://www.w3schools.com/sql/sql_union.asp
If returning all fields then it is simply

SELECT *
FROM tableOne
UNION
SELECT *
FROM tableTwo
Forum: Queries 03-14-2019, 05:27 AM
Replies: 7
Views: 90
Posted By MajP
Re: Need help with queries that span tables.

You can combine all six Sales_Master into a single query using a union query. From there you can make one single table, if necessary.
Forum: Queries 03-14-2019, 05:15 AM
Replies: 7
Views: 90
Posted By plog
Re: Need help with queries that span tables.

Why? This isn't the proper way to house data. Why isn't all the Sales_Master data housed together properly?

I don't understand your example's in parenthesis. Are those row names or values in a...
Forum: General 03-04-2019, 03:16 PM
Replies: 31
Views: 557
Posted By The_Doc_Man
Re: Need advice on DB design.

As soon as you say "with its own database" you are opening up Pandora's box, which contains a can of worms. Multiple databases mean you CANNOT create a formal relationship between / among the files...
Forum: General 03-04-2019, 09:23 AM
Replies: 31
Views: 557
Posted By Mark_
Re: Need advice on DB design.

For a moment, lets looks at the sales person and Product tables.

What information do you have/need per salesperson?
What information do you have/need per product?

If you can store location...
Forum: General 03-03-2019, 10:42 PM
Replies: 31
Views: 557
Posted By The_Doc_Man
Re: Need advice on DB design.

Mark's comments are on-point. In order to know how much to store, you must first determine how much you intend to ask - and then more or less work backwards - to see what it will take to support...
Forum: General 03-03-2019, 05:29 PM
Replies: 31
Views: 557
Posted By Mark_
Re: Need advice on DB design.

Just to make sure, your project is to do reporting on large data sets. Does this also involve data mining or is this for simple analysis or tracking?

More to the point, will you be required to...
Forum: General 03-03-2019, 04:38 PM
Replies: 31
Views: 557
Posted By The_Doc_Man
Re: Need advice on DB design.

Looking at the structure again, I changed my mind about something. You are making a lot of "roll-up" reports but I don't see it likely that you will want to go back and reference anything by the...
Forum: General 03-03-2019, 04:06 PM
Replies: 31
Views: 557
Posted By The_Doc_Man
Re: Need advice on DB design.

In a word, yes. Tables are either linked or local. Local tables are in your primary app file which is usually your front-end or FE file. Linked tables are in another file that is usually your...
Forum: General 03-03-2019, 08:01 AM
Replies: 31
Views: 557
Posted By The_Doc_Man
Re: Need advice on DB design.

Definitely not a separate database. Having extra files means you make it possible to keep more data in Access back end files but every new BE file adds maintenance headaches. Further, you cannot...
Forum: General 03-03-2019, 07:17 AM
Replies: 31
Views: 557
Posted By MickJav
Re: Need advice on DB design.

Don't think this has been brought up after reading a number of comments I notice the 2gb limit is quoted but there is also the table level lmit of 1Gb I don't think think thats changed with office...
Forum: General 03-02-2019, 03:51 PM
Replies: 31
Views: 557
Posted By Mark_
Re: Need advice on DB design.

Let's say the application has these characteristics:


There are 800,000 salespersons each with unique ID (I).
The number of product groups being sold could be 6 to 12 (P).
Each ...
Showing results 1 to 25 of 128

 
Forum Jump



All times are GMT -8. The time now is 02:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World