John Big Booty

[manovice]

John Big Booty or ANYONE who can assist

You seem extremely knowledgeable about access so hopefully you will be willing to help me. I have been thrown in to the world of database building and I am barely treading above water here. I have posted twice today to no avail as of yet.

My main form pulls from a query that pulls from two tables. I need to be able to store facilities in one table and reviews of the facilities in the other. However, my tables are not updating accurately.

Could you possibly assist me?
Thank you
Nicole

 

[John Big Booty]

If you can post a cut down version of your DB with the portion that is giving you trouble I'll have a look at it for you.

 

[manovice]

I am not sure what you mean by a cut down version. I have only populated about 7 rows in two tables with fake data. Then I have a few queries with little info and also three forms. Can I just post the whole thing?
I am sorry I seem so stupid, but I think I used the correct user name with MicrosoftAccessNovice!!!

Thanks

 

[manovice]

I actually have to leave the computer for a while so I am going to post what I have. With juggling kids and work, it is time to pick them up.
So here is where I am at right now.

My main problem at the moment is that when you open the form AllReviewInfo and you enter a record and click save, the form refreshes and all looks fine. However, when you go back to the table and query... the EstInfo Table (which should just store the records pertaining to each establishment and should not be duplicated) another record for the same establishment is being entered. I would like to keep the establishment ID consistent amongst all tables, etc.

So, hopefully, you can provide me some insight. I am about to demand that my boss outsource this and have someone who knows what they are doing create this project!
Thanks so much for your help with this. I really do appreciate it.
Nicole

 

[John Big Booty]

OK. First, have a bit of a read up on normalisation before your go too much further.

Second, have a think about implementing a naming protocol, so that your DB objects are easily identifiable. For example Tables; TBL_MyTableName, Forms; FRM_MyFormName, Queries; QRY_MyQueryName, Reports; RPT_MyReportName. Avoid using spaces in object name as this will save you untold grief in the long run. Limit your names to alphanumerics and underscore (_).

I'll post further once I've worked out what's (not) going on in the DB.

 

[John Big Booty]

I actually have to leave the computer for a while so I am going to post what I have. With juggling kids and work, it is time to pick them up.
........

I've heard of juggling chainsaws, and flaming torches, but kids is that safe especially as you seem to have dropped one

Sorry back to the serious business at hand

...

My main problem at the moment is that when you open the form AllReviewInfo ....

I couldn't find that form so I presume you meant ReviewInfoForm. As posted I couldn't get that form to save I kept on getting a message telling me a related record was required in Table "EstInfo". I managed to get around that by removing the requirement for referential integrity in the Relationships window.

Having done that I couldn't duplicate the behaviour you describe

...

...However, when you go back to the table and query... the EstInfo Table (which should just store the records pertaining to each establishment and should not be duplicated) another record for the same establishment is being entered. I would like to keep the establishment ID consistent amongst all tables, etc.

....

Additionally there seems to be a filter on the ReviewInfoForm.

 

[John Big Booty]

I've had a play with your DB, and made same modifications to it.

I hope I've headed in the right direction, if not I hope you'll be able to pick up the trail and do your own modifications to get it back on track.

There is still a lot of polishing that can be done to make it more user friendly.

One thing I would consider would be to copy all the Zip codes from here and import hem into TBL_City.

 

[SOS]

You seem extremely knowledgeable about access so hopefully you will be willing to help me. I have been thrown in to the world of database building and I am barely treading above water here. I have posted twice today to no avail as of yet.

My main form pulls from a query that pulls from two tables. I need to be able to store facilities in one table and reviews of the facilities in the other. However, my tables are not updating accurately.

Could you possibly assist me?
Thank you
Nicole

Just a note about this - you really should just post your question and not to a specific person as it is the forum as a whole that can give you help and, at times it may be much faster than trying to get a specific person to help. That is the beauty of forums - a whole lot of help available.

 

[manovice]

Thank you John Big Booty for your assistance. I will look in to your suggestions and let you know how things work out.

And SOS, I am very familiar with forums and understand that it is a group effort, but I had posted at least four posts this week and had gotten extremely minimal responses. In looking at the posts, I saw that John Big Booty had given many replies and I happened to be on the forum when he was so I wanted to be a little more direct in getting someone's attention.

I apologize for seemingly ignoring other participants, but the point of the forum is to also get responses, of which I was not getting any until I posted this thread.

Again, sorry for any hurt feelings, but I would also be happy with any suggestions that you have on my problems here.

Thank you
Nicole

 

[manovice]

Re: John Big Booty Or ANYONE INTERESTED

Okay, I have finally gotten a chance to look at what you sent and it looks good. I also read up on the normalization link that you sent and that made sense.
First Question: Shouldn't all of the tables somehow be linked together?
As I was going through and changing some things and information, I see that I need the additional tables do not update. I am assuming that I need to create links, but don't want to do my best at screwing this up.

Second question: I don't need the prompt for the zip and state so I removed that from the query, but it is still coming up when I open up the forms. Any ideas on how to remove that?

I guess we can start there. And anyone that has suggestions is welcome to put input.
Thanks
Nicole

 

[John Big Booty]

Re: John Big Booty Or ANYONE INTERESTED

Okay, I have finally gotten a chance to look at what you sent and it looks good. I also read up on the normalization link that you sent and that made sense.
First Question: Shouldn't all of the tables somehow be linked together?
As I was going through and changing some things and information, I see that I need the additional tables do not update. I am assuming that I need to create links, but don't want to do my best at screwing this up.

........

The tables are all link with either a Primary Key and foreign key. I just haven't explicitly expressed those links in the relationships window.

....

Second question: I don't need the prompt for the zip and state so I removed that from the query, but it is still coming up when I open up the forms. Any ideas on how to remove that?

........

You will also need to remove the ZIP and state from the underlying table, as that is where the data is stored, and what is prompting the form to ask for it.

Have a bit of a play with it and if you get stuck post the DB again with your changes.

Good luck and have fun

 

[John Big Booty]

Just a thought regarding the ZIP and state. I always find that it is easier to design that sort of thing in at the start, rather than try and add it at a latter date, when someone says; That's good, do you thin you could add ZIP code and state to that.

I learned that lesson when I made a DB for one of my work colleges. At the out set I asked him, how many clients he was going to use the DB for? His answer, just one. I asked him, now your sure about that? To which answered yes. A couple of weeks after I'd finished the DB and he had been using it, he came into my office and asked if I could expand the DB to work on some other clients