Adding a field to a database

[gregh911]

Pretty much an Access noob here. I inherited a database for membership in a group I belong to. It was pretty much spelled out to me how to accomplish most tasks. The problem now is that we added a new membership level that I need to add to the database. I'm pretty sure I have added it OK, but cannot understand how to add it to a query properly. What I am working with is this; we have always had 1, 3 & 5 year memberships. We have now added a lifetime membership (called Dues For Life). The new field in the database is called DFL. When I try to add it to a query, it is not showing up in the results. Sorry if my terminology is off. I'm just trying to understand and everything I read is for building databases from scratch. Can someone point me in the right direction? Access for dummies? Thank you in advance.

[ranman256]

You should have a table that holds the values for membership terms....tMemTerms
1 yr
3
5 yr
Life
in it ,just add new values when needed.

in the user form to enter members, set a combo box to read this table to offer the options.
nothing in your table design need change.

[Bulzie]

I agree with ranman that it should be in 1 field. But are you saying that you have a query which has the table where you added the field and you selected that field and see it as a column in the query and when you run the query you do not see that field in the output? If so are you sure the Show box is checked on that column?

[gregh911]

Thank you for the quick replies. I'm sure that I'm in over my head here. ranman, your response sounds like a foreign language to me. Bulzie, yes the show box is checked. Where I'm having a problem is in the query because all the other memberships have an expiration date. This one does not. So I try to add the field to the query as an "or" (hope that makes sense), but when I run the query it doesn't show up.

[trevor40]

Thank you for the quick replies. I'm sure that I'm in over my head here. ranman, your response sounds like a foreign language to me. Bulzie, yes the show box is checked. Where I'm having a problem is in the query because all the other memberships have an expiration date. This one does not. So I try to add the field to the query as an "or" (hope that makes sense), but when I run the query it doesn't show up.

Debug...

first copy the query to a new one, for testing.
second make it a standard select query, ie no conditions, does this display all the data when you run it?
if so then add any conditions one by one until it stops working as required, this will show you why it's not working. now get back to the forum with this information and see what people here can do then.

[Bulzie]

At top left where you have option for Design View, Select SQL View, then copy and paste that code in here so we can take a look. Few more questions:
Is the new field DFL an actual new field in a table or is it a checkbox in an option field? Or is DLF something you added to a value list that had the 1;3;5 values already?

[gregh911]

Debug...

first copy the query to a new one, for testing.
second make it a standard select query, ie no conditions, does this display all the data when you run it?
if so then add any conditions one by one until it stops working as required, this will show you why it's not working. now get back to the forum with this information and see what people here can do then.

I will give this a try. I've been so afraid of screwing up the existing database. thank you.

[gregh911]

At top left where you have option for Design View, Select SQL View, then copy and paste that code in here so we can take a look. Few more questions:
Is the new field DFL an actual new field in a table or is it a checkbox in an option field? Or is DLF something you added to a value list that had the 1;3;5 values already?

The DFL is a new field. The other memberships just have expiration dates, with no 1;3;5 value at all. I will get back with a copy of one of the queries I'm trying to add it to.

[Bulzie]

I've been so afraid of screwing up the existing database. thank you.

You should always have a good backup before doing any changes. Before doing anything else I would copy the database and append _011217 to it so you know it is from today. Then if something does not work you can always put the original one back. Hope you are using a linked BE database for the tables?

[gregh911]

OK here is one of the queries. Our group has many sub-groups (chapters). This query is run to give the chapter all their current members and their expiration dates. The -3 in the date field is because we have a 3 month grace period.

SELECT [Big Giant Database].Group, [Big Giant Database].[File As], [Big Giant Database].Joined, [Big Giant Database].Expires, [Big Giant Database].Life, [Big Giant Database].[Label 1], [Big Giant Database].City, [Big Giant Database].Address, [Big Giant Database].St, [Big Giant Database].Zip, [Big Giant Database].Phone, [Big Giant Database].[E-Mail], [Big Giant Database].Number, [Big Giant Database].Life, [Big Giant Database].DFL
FROM [Big Giant Database]
WHERE ((([Big Giant Database].Group)="olympia") AND (([Big Giant Database].Expires)>DateAdd("m",-3,Date()))) OR ((([Big Giant Database].Life)="life") AND (([Big Giant Database].DFL)="DFL"))
ORDER BY [Big Giant Database].Group, [Big Giant Database].[File As];

[Bulzie]

So when you run that you get records and all those data columns except DLF column?

AS trevor suggested, Run just this part and see if you get the DFL column:

SELECT [Big Giant Database].Group, [Big Giant Database].[File As], [Big Giant Database].Joined, [Big Giant Database].Expires, [Big Giant Database].Life, [Big Giant Database].[Label 1], [Big Giant Database].City, [Big Giant Database].Address, [Big Giant Database].St, [Big Giant Database].Zip, [Big Giant Database].Phone, [Big Giant Database].[E-Mail], [Big Giant Database].Number, [Big Giant Database].Life, [Big Giant Database].DFL
FROM [Big Giant Database]

[gregh911]

So when you run that you get records and all those data columns except DLF column?

AS trevor suggested, Run just this part and see if you get the DFL column:

SELECT [Big Giant Database].Group, [Big Giant Database].[File As], [Big Giant Database].Joined, [Big Giant Database].Expires, [Big Giant Database].Life, [Big Giant Database].[Label 1], [Big Giant Database].City, [Big Giant Database].Address, [Big Giant Database].St, [Big Giant Database].Zip, [Big Giant Database].Phone, [Big Giant Database].[E-Mail], [Big Giant Database].Number, [Big Giant Database].Life, [Big Giant Database].DFL
FROM [Big Giant Database]

Which brings up another dumb question, how do I enter and run a new query?

[trevor40]

Browse through your tool bar at the top of the screen, if you have the query window open you should see an icon that is for running the query and display the results on the screen, mouse over each icon to find it, it should say "run', and perhaps a big exclamation icon.

[trevor40]

the exclamation icon in the tool bar runs a query and displays the results on the screen