Question

3 TABLES (3 DATES):

DATE	USERNAME	ORDER	STATE
6/21/20	John	Premium	AL
6/21/20	Tyler	Trial	GA
6/21/20	Karen	Plus	FL
6/21/20	Jordan	Regular	WA
6/21/20	Kerry	Plus	DC
6/21/20	Logan	Regular	CA
6/21/20	Aaliyah	Trial	WI
6/21/20	John	Premium	AL
6/21/20	John	Premium	AL
6/21/20	Kerry	Plus	DC
6/21/20	Tom	Plus	VA

DATE	USERNAME	ORDER	STATE
6/22/20	Meg	Premium	OK
6/22/20	Jannet	Plus	OK
6/22/20	Tom	Plus	VA
6/22/20	Garry	Trial	WA
6/22/20	Blake	Plus	WA
6/22/20	Blake	Plus	WA

DATE	USERNAME	ORDER	STATE
6/23/20	Paul	Regular	UT
6/23/20	Aaliyah	Trial	WI
6/23/20	Pat	Premium	CA
6/23/20	John	Premium	AL
6/23/20	Will	Plus	NM
6/23/20	Erin	Regular	LA
6/23/20	Garry	Trial	WA

SQL Query to apply in Access:

Write a query to show how many times each user ordered on each day and the total amount of times the user has ordered.

Now write a query to show the information above but grouped by state for each day and total.

Ive already received these instructions: Didn't work well. If some on could post screen shots that would be great. I know nothing about getting this data from Excel to Access. If there is a better method please let me know!

1. Click on create tab and query design and select all the types of fields you want to include in the query.

2. Select or checkin the rows you used and also add the count of the filed you need.

3. Repeat this for each date to get the number of orders received per day.

4. To group by,

a.On the Design tab, in the Show/Hide group, click Totals.

The Total row appears in the design grid and Group By appears in the row for each field in the query.

b. In the Total row, click the field that you want to count and select Count from the resulting list.

Answer 1

BEGIN TRANSACTION;
/* Create a table called NAMES */
CREATE TABLE T1(datee date, Username varchar(20), ORDERR varchar(20), State varchar(10));
CREATE TABLE T2(datee date, Username varchar(20), ORDERR varchar(20), State varchar(10));
CREATE TABLE T3(datee date, Username varchar(20), ORDERR varchar(20), State varchar(10));
/* Create few records in this table */
INSERT INTO T1 VALUES('6/21/20','John','premium','AL');
INSERT INTO T1 VALUES('6/21/20','Tyler','Trial','GA');
INSERT INTO T1 VALUES('6/21/20','Karen','Plus','FL');
INSERT INTO T1 VALUES('6/21/20','Jordan','Regular','WA');
INSERT INTO T1 VALUES('6/21/20','Kerry','Plus','DC');
INSERT INTO T1 VALUES('6/21/20','Logan','Regular','CA');
INSERT INTO T1 VALUES('6/21/20','Aaliyah','Trial','WI');
INSERT INTO T1 VALUES('6/21/20','John','Premium','AL');
INSERT INTO T1 VALUES('6/21/20','John','Premium','AL');
INSERT INTO T1 VALUES('6/21/20','Kerry','Plus','DC');
INSERT INTO T1 VALUES('6/21/20','Tom','Plus','VA');

INSERT INTO T2 VALUES('6/22/20','Meg','Premium','OK');
INSERT INTO T2 VALUES('6/22/20','Jannet','Plus','OK');
INSERT INTO T2 VALUES('6/22/20','Tom','Plus','AL');
INSERT INTO T2 VALUES('6/22/20','Garry','Trail','WA');
INSERT INTO T2 VALUES('6/22/20','Blake','Plus','WA');
INSERT INTO T2 VALUES('6/22/20','Blake','Plus','WA');

INSERT INTO T3 VALUES('6/23/20','Paul','Regular','UT');
INSERT INTO T3 VALUES('6/23/20','Aaliyah','Trial','WI');
INSERT INTO T3 VALUES('6/23/20','Pat','Premium','CA');
INSERT INTO T3 VALUES('6/23/20','John','Premium','AL');
INSERT INTO T3 VALUES('6/23/20','Will','Plus','NM');
INSERT INTO T3 VALUES('6/23/20','Erin','Regular','LA');
INSERT INTO T3 VALUES('6/23/20','Garry','Trial','WA');
COMMIT;
/* Display all the records from the table */
SELECT * FROM T1;
SELECT * FROM T2;
SELECT * FROM T3;