TShirt Query, (newbie!!!) (1 Viewer)

PeggyC09 · Apr 10, 2018

So I have much larger database (1200+) but am struggling to put together this query.

I need to know how many t-shirts of each size, in each color, sorted by "print" and excluding withdraw.

Because of the wordpress-download to excel-upload to access, and then ordering "more later," I'm stuck with the format in this of the size given and then adding extra shirts as they order them.

I have teams (like Ice Crew) who I need to pass on size to but I don't order for. I can't have those shirts in the total.

People will withdraw and I need to make sure those don't get ordered.

I hope this makes sense.

I'm just so confused!!!

plog · Apr 10, 2018

I hope this makes sense

It does not. You ran into a room shouted everything that was on your mind in 10 seconds and then threw a database at us. You posted in the query section, so I assume you want a query.

The good news is, I don't need an explanation, that usually hampers things actually. I just want data. You have shown us what you have for starting data, now show me what data you hope to end up with.

Again, using the database you posted show me the data you expect your query to return.

PeggyC09 · Apr 10, 2018

Sorry about that.

What I need is to know
1. How many shirts to order total.
2. How many by size, color, and what is printed.

I'm struggling with excluding the ice team and those who withdraw without deleting their information. (maybe a second query without those first, to build on?).

I can't figure out how to add the "extra shirt order" in to the information I'm given on their application.

My database has a separate table for the color of the shirt which is based on the team and I tried to repeat this.

plog said:
It does not. You ran into a room shouted everything that was on your mind in 10 seconds and then threw a database at us. You posted in the query section, so I assume you want a query.

The good news is, I don't need an explanation, that usually hampers things actually. I just want data. You have shown us what you have for starting data, now show me what data you hope to end up with.

Again, using the database you posted show me the data you expect your query to return.

PeggyC09 · Apr 10, 2018

plog said:
Again, using the database you posted show me the data you expect your query to return.

Data Should be (or close to it...):
13 shirts total

Orange, Beer Garden, "Beer Garden"
1 medium
3 XL

Pink, Camp Host, "Volunteer"
2 Medium
2 Large

Pink, Camp Marking, "Volunteer"
2 Medium

Pink, Volunteer Support, "Volunteer"
1 S

Green, Guest Services, "Jessicas Team"
2 XL

Nothing on Withdraw or Ice Team

plog · Apr 10, 2018

Just a second.

plog · Apr 10, 2018

Why nothing on the Ice Team?

plog · Apr 10, 2018

Also, you really need to fix your tables.

1. You honestly have fields called 'Field1' and 'Field2'? Use better, less generic names.

2. You are storing numeric data as Text (ExtraShirts), if you want to do math on that field, which you do, you need to store it as a numeric field.

3. You need to relate your tables properly with foreign keys (https://en.wikipedia.org/wiki/Foreign_key). TeamColor has an ID field, but you are not using it. That is the value that should go into Volunteers.Team, instead of TeamColor.Field1

Fix your table, then tackle other issues.

PeggyC09 · Apr 10, 2018

The supervisor gets his own from a different source, so just needs the info.

plog said:
Why nothing on the Ice Team?

PeggyC09 · Apr 10, 2018

Fixed it and reattached.

plog said:
Also, you really need to fix your tables.

1. You honestly have fields called 'Field1' and 'Field2'? Use better, less generic names.

2. You are storing numeric data as Text (ExtraShirts), if you want to do math on that field, which you do, you need to store it as a numeric field.

plog · Apr 10, 2018

The below SQL will do it:

Code:

SELECT TeamColor.Color, TeamColor.Team, Volunteers.Print, Volunteers.Size, Sum(Nz([ExtraShirts],0)+1) AS Shirts
FROM Volunteers INNER JOIN TeamColor ON Volunteers.Team = TeamColor.Team
GROUP BY TeamColor.Color, TeamColor.Team, Volunteers.Print, Volunteers.Size
HAVING (((TeamColor.Team)<>"Ice Crew"));

However, you should fix your tables so that you don't have to specifically exclude Teams. I would add a new field to TeamColor that is Yes/No and designates if they should be included in the query. That way you can handle Withdraw and Ice Crew properly.

Also, use foreign keys properly

3. You need to relate your tables properly with foreign keys (https://en.wikipedia.org/wiki/Foreign_key). TeamColor has an ID field, but you are not using it. That is the value that should go into Volunteers.Team, instead of TeamColor.Field1

PeggyC09 · Apr 10, 2018

I'm attaching the database again but (NEWBIE)... and I don't know if this is what you meant by fixing my relationships.

PeggyC09 · Apr 10, 2018

When I pasted that SQL code in it returned nothing. I'm so lost. I'm attaching it again. Thanks for working with me. I'm buying a book on SQL.

plog · Apr 10, 2018

It worked on one version of your database. The one where you incorrectly linked the fields Team to Team in each table.

In the latest version you have changed the value in Volunteers.Team to the text equivalent of the number in TeamColor.ID--you did it incorrectyl. I posted a link about foreign keys prior, read up on that.

When you use a foreign key, the 2 fields need to share a compatible type. Volunteers.Team should be a number type to hold the number value from TeamColor.ID.
As it stands now, you cannot properly link your tables because the fields that you need to relate are incompatible types.

PeggyC09 · Apr 11, 2018

OK. I got it to work, but it's still including the withdraw team. When I tried to add it like you did on the ice team <> "withdraw" it comes up with an error.

I'm not sure how to add an additional condition like this.

I also added the "yes/no" field as you suggested so perhaps there is another way to go about this?

Thanks in advance.

ypma · Apr 11, 2018

Not hijacking this post, just suggestions . Why not us the true/false field for you r criteria , if set to false you would would not need to ice team <> "withdraw, which should read <>ice team And <>"withdraw"

hope i have understood your requirements .
#
YPMA

plog · Apr 11, 2018

Data looks good now. I would use this query:

Code:

SELECT TeamColor.Color, TeamColor.Team, Volunteers.Print, Volunteers.Size, Sum(Nz([ExtraShirts],0)+1) AS Shirts
FROM TeamColor INNER JOIN Volunteers ON TeamColor.ID = Volunteers.Team
WHERE (((TeamColor.Included)=True))
GROUP BY TeamColor.Color, TeamColor.Team, Volunteers.Print, Volunteers.Size;

That way, you just change data to exclude/include teams and it flows through to the query without manually changing it.

PeggyC09 · Apr 17, 2018

Finally getting back to this issue. Thanks for the suggestion.
I will try that!

ypma said:
Not hijacking this post, just suggestions . Why not us the true/false field for you r criteria , if set to false you would would not need to ice team <> "withdraw, which should read <>ice team And <>"withdraw"

hope i have understood your requirements .
#
YPMA

TShirt Query, (newbie!!!) (1 Viewer)

Registered User.

Attachments

Banishment Pending

Registered User.

Registered User.

Banishment Pending

Banishment Pending

Banishment Pending

Registered User.

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Registered User.

Banishment Pending

Registered User.

Similar threads

Users who are viewing this thread