Append query not working

[pretzman21]

Hello,

I am trying to create a database for my bowling league. For starters, I intend on having a query prompt me to enter 3 games using the append query function as seen below. When I run the query, it does prompt me to enter the 3 games, and I am able to input them. However, when going back to the original table of Game, nothing has been entered into the columns of Game1, Game2, Game3. I do understand that I want to this be an append, not an update as I want new data to be input. I suspect that this is an issue with microsoft access, but I can't be certain. Access does not output any errors of any kind either which is not helpful. Below is the sql view that was created when I put together the items from the query design view. Also, this is Microsoft Access for office 365, but it states that its access 2007 with 2016 file format.

INSERT INTO Game ( Game1, Game2, Game3, GameID )
SELECT Game.Game1, Game.Game2, Game.Game3, Game.GameID
FROM Game
WHERE (((Game.Game1)=[Enter game 1]) AND ((Game.Game2)=[Enter game 2]) AND ((Game.Game3)=[Enter game 3]));

My question is, how can I get this to work? Do I need to apply any particular Microsoft updates? Is there something I can do to the syntax of this query? Any advice would be appreciated.

 

[Joe4]

Welcome to the Board!

Why do you have an Append Query at all?
If you just create an Entry Form off of your Game table, you can enter new data directly into the Form.

No need for an Append Query. An Append Query is usually used to copy EXISTING (not new) data over from one Table (or Query or data listing) to another Table.

 

[welshgasman]

If I understand you correctly, you want a quick and dirty method to add the data, rather than use a form.?

I've just created an append query, where it asks me for all the fields.

The sql is as below

INSERT INTO Table1 ( ID, CreatedDate, CreatedBy, AmendedBy, Data )
SELECT [MyID] AS ID, [Date] AS CreatedDate, [Yourname] AS CreatedBy, [Amendname] AS AmendedBy, [EnterText] AS Data
FROM Table1;

There is no need for the WHERE clause.
This then prompts you for each field in turn.

That said, a form is a better way.
HTH

Hello,

I am trying to create a database for my bowling league. For starters, I intend on having a query prompt me to enter 3 games using the append query function as seen below. When I run the query, it does prompt me to enter the 3 games, and I am able to input them. However, when going back to the original table of Game, nothing has been entered into the columns of Game1, Game2, Game3. I do understand that I want to this be an append, not an update as I want new data to be input. I suspect that this is an issue with microsoft access, but I can't be certain. Access does not output any errors of any kind either which is not helpful. Below is the sql view that was created when I put together the items from the query design view. Also, this is Microsoft Access for office 365, but it states that its access 2007 with 2016 file format.

INSERT INTO Game ( Game1, Game2, Game3, GameID )
SELECT Game.Game1, Game.Game2, Game.Game3, Game.GameID
FROM Game
WHERE (((Game.Game1)=[Enter game 1]) AND ((Game.Game2)=[Enter game 2]) AND ((Game.Game3)=[Enter game 3]));

My question is, how can I get this to work? Do I need to apply any particular Microsoft updates? Is there something I can do to the syntax of this query? Any advice would be appreciated.

 

[Joe4]

Either way, you are entering data into a Form or boxes that pop-up.

One big advantage to using Forms is the ability to control the data being entered (through data types, validation, etc).
It reduces the chances of errors and bad data being entered.

 

[welshgasman]

Access has wizards for a lot of objects, one of them being forms.
In 2007, you would select your table, then the Create tab, then Form.

That's it, a basic form to start with.

 

[pretzman21]

You all are amazing for responding so quickly. I'm in IT myself and I might have tried to make this more complicated than I needed to. I did try to make a form, and that seems to be a much better option than my original idea of working with the append query. Already I am farther than when I was, and I thank you all for that.

I guess I have another question while this thread is still active. Is there a way in the form to create a drop down list that filters by team name automatically? I know how to make the drop down, but how would i make it sort by just clicking the drop down box? would i have to create another table, or query?

 

[Joe4]

I guess I have another question while this thread is still active. Is there a way in the form to create a drop down list that filters by team name automatically? I know how to make the drop down, but how would i make it sort by just clicking the drop down box? would i have to create another table, or query?

I am not quite clear what you mean. Maybe you can walk us through an example?
Are you just wanting a drop-down box to fill out the team name, instead of typing it in manually?
If so, it is recommend to create a separate table of all possible team names. And then you can use this to populate the Combo Box for that field.

 

[pretzman21]

I will do my best to explain my intentions, and maybe that will help:

-My intent is to create a database for my bowling league. There will be 100 bowlers on 24 teams that will play 3 games. I will be tracking cumulative average and handicap, points won (individual and team) on a 33 week basis.
-Each week, I would like to be able to add the 3 games to each bowler and have their average and handicap update based on those 3 games bowled.
-I would like to be able to select the given week from a drop down, and then the team from the drop down. When I drop down the team, i'd like the bowlers from that team only to show up, and then i will drop down and pick the next team.

I guess my question now is, how many tables do I need? Here is a list of what tables i think are needed and what should be contained on those tables:

Table - TeamName
Column: Teamnames of "Team 1" through "Team 24"
Bowler name
Table-Bowler
Column: Game1, Game2, Game3, Average, Handicap

Table-Schedule
Column: Week 1, Week 2, Week 3, etc

 

[Joe4]

It is important to try to create Normalized Tables. If you are not familiar with Normalization, you will want to read up on that: https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

A few main points when designing tables:
- You should not have multiple fields in the same table that are similar (i.e. you should not have Game1, Game2, Game3 columns, but rather a Game Number field and a Game Score field - so you would have three records instead of three columns for three different games)
- You should not store anything which can be calculated from the data that resides in the table (calculations should be done dynamically in queries and not stored at the Table level)


From a high-level, here are some of the tables I think you need, with columns listed:

TeamName
Team Name

Bowlers
Bowler Name
Team (from TeamName)

Schedule
Week Number
Date

Results
Week Number (from Schedule)
Team (from TeamName)
Game Number (i.e. 1,2,3)
Game Score