Multiple Tables with Same Columns

[jaytee716]

Hi,

I am hopeing that someone can help me. I have taught myself Access in the past month by searching the WWW for information and using the Help files, but now I am stuck.

I have 4 tables, all with the same columns; one of which is an ID number which is an Autonumber (so in each table I have a 1-xxx). I want to be able to report from all columns in each table, but I am not sure what I should do. I tried many things, one of which was to run a Query, but it listed all columns separately from each table even though they had the same name. What I am looking to do is to list each column "Type", "Colour", "Make", etc. from all 4 tables and combine them into one heading for reporting purposes.

Can anyone please help me?? I have a meeting next week to show my progress and I was so excited that I taught myself all that I accomplished but now I am stuck and don't know where to turn.

In advance, thank you for 1) reading my post and 2) helping me accomplish my brick wall!

Jim

PS. I forgot, when I was messing around with the Queries, it also generated multiple results of the same record. So record 1 in table one appeared like 6 times!! Sorry that I forgot to mention :-(

 

[boblarson]

Unfortunately, I think you still have some ways to go. You really shouldn't have 4 tables with the same columns. You need to read up on Normalization.
http://support.microsoft.com/kb/283878

Also, for queries, go to this tutorial to see how you write/use them:
http://www.functionx.com/access2003/Lesson17.htm

 

[jaytee716]

RE Normalization

Thank you for the reply!!

I actually read that, but perhaps I don't understand.

The reason that I have 4 tables is because I have 4 modules of the same software system that I set my database up to test. All have the same columns, but the "scripts" are different. Here is what my colums look like:
Task ID (Autonumber)
Test Script
Expected Results
Actual Results (user filled in)
Status (seperate table not included in the other 4 tables)
Account # Tested (repeated 4x in each table)
User Name (seperate table not included in the other 4 tables)
Severity (seperate table not included in the other 4 tables)
Test Date

So the reason that I made 4 seperate tables is so that the user can select from the main form the system that they are testing and access the scripts from that particular form. For reporting purposes we need to combine all of the forms as one unit so that we can see where we are at over all. The user is the only one that cares about each of the 4 individual forms, we care about the combined 4 forms as well as need to seperate them for the interface setup.

I do know that I have a lot to learn, but I am trying the best that I can. Again, thank you for your help!

 

[georgedwilkinson]

You don't need 4 tables. You're causing yourself tons of work and your program will not work right for very long. It is also the reason you're having problems with your report.

You can put a discriminator column on the single table you replace the 4 with to meet your goals. Then for the users who want scripts for an "x", you can filter the form to only show/edit records for script type "x".

If you insist on doing it wrong, look up a union query to make this sub-problem of the eventual flood of problems you're going to have go away.

 

[jaytee716]

Filtering Form

Ok, so that makes sense. So combine all of the 4 tables into one, add a column to distingush what system the user is testing....but, how do I create seperart forms (4 of them) for each system that I want my user to test? I know how to filter queries (after a couple days of serching) but not forms.

Can you help?

Thank you again!

Jim

 

[boblarson]

You don't need to create multiple forms. Just load the recordsource for one form depending on what you want.

So, you can create 4 queries (you don't really need to do that either but for simplicity's sake, since you're probably not quite ready to go that far yet), and then just assign the correct query to the form based on whatever you decide you want.

Like:

Me.Recordsource = "qryTesting1"

 

[jaytee716]

sorry......

I guess I don't understand George. Here is what I am working with. I understand having one table with the information, but how?? that is where I don't understand. How do I have all of my tables pull from one database?

Again, thank you!!

 

[georgedwilkinson]

There's a definite problem with terminology.

You need a single database, if you are using more than one your talent is above mine and you should ignore my advice.

Within that one and only database, create a single table that is identical to your 4 tables, except it has an additional column to indicate which of your original tables had been the table you would have otherwise used. Copy all the data from the 4 tables into the single table (use an append query).

Delete or otherwise ignore the 4 old tables.

Create 4 queries using the query wizard or grid. Make all 4 queries identical except the criteria. Use the new column to discriminate which query represents which of the tables you had previously had 4 of.

Open the form wizard. Use one of the queries as your "table" for the main form.

When the main form is completed and working (don't even bother going forward until it works), put a control or controls on the main form to help select which of the original tables you will be working with (they will be unbound). I envision a group of option buttons but you can use whatever you think you can work with (combo box, xyzcontrol, etc.). Don't do anything with them yet!

Open the properties window for the field that represents your discriminator field. Set the Default property of that field to the value of the discriminator of the view you used to create the form.

Now you can mess with your unbound control(s). In the properties for the unbound control(s), go to the "Event" tab and click on the white space next to "On Change". Click on the button with the ellipses (...), select code builder (you can use any of the other options but I will be unable to help you after that) and it will open the VBE with the shell code already opened for editing.

In this code, you will read the value of the control(s) and set the "Control Source" and the Default of your discriminator column to the value that is appropriate for the value they selected in this control.

It is at this point that I expect you will be posting back asking what to do next. By that time, you will have fixed all the problems that the experts on this forum object to and tons of people will be ready to help you. If they don't before I get to it, I will help you again. You just gotta fix the glaring problem with your foundation before most of the geeks here will help you paint the walls.

 

[jaytee716]

Will Try now..

Thanks George.... I'm going to try that now and let you know what happens!

Jim

 

[jaytee716]

So Far So Good!

Update....I am doing this now and can't believe that I over looked this and why I designed so many extra tables. I made a new DB to test this, now I am going to modify the one that I have been working on and hope that all goes well and it works!! Will update again. Thanks again George!

 

[boblarson]

Update....I am doing this now and can't believe that I over looked this and why I designed so many extra tables.

It is all about learning. It takes time and effort and if you are wise, you also take knowledge offered by those who have gone before, which is what you appear to be doing and that is good.

Good luck with everything. Believe me there is a LOT to learn about Access and all, and it takes time and even mistakes. I have been doing this for 11 years and I don't know everything about it. I keep learning things I didn't know, or someone will correct my "current knowledge" with information that is either correct, or a better way of doing it. The benefit of these forums is that you have a lot of experience to draw from. That's why I love it here. I'm always learning something new.

 

[jaytee716]

That's Right!!

I have relied a lot on others though the Internet to help me. Like I said, I have had no knowledge of Access prior, and do have a lot to learn. But I did it. I am to a point where I can present this next week to my boss and I am sure that he will be happy. I am however stuck with a query and don't know if I should post here or in another section.

Here's what I am trying to do. In my primary table (yes, one table with all of the information lol [finally!!]) I have one field where the user can status the record in their form based on another table used for the drop down selections (listed there is Approved, Failed, Pending Batch. By default the field (column) is blank, meaning that the record has not been tested. But when I run a pie graph so that we can have an overall picture of what is completed and what is outstanding. So in the graph the slice without a status is labeled slice 1. I want this to be labeled "Not Yet Worked" or something similar. I tried to run a query iif "" then "Not Yet Worked" but this errored out. Not sure what else to try. Any suggestions??

Thank you for your help and your words of wisdom!! I really do appreciate all the help.

Jim

 

[georgedwilkinson]

Iif syntax is:

Iif(mycontrol = "", myothercontrol = "Not Yet Worked", dothisif conditionisfalse)

or, just set the default value of that control to "Not Yet Worked" in the properties.

It's good to see a light bulb go on.

Please feel free to ask any followups.

 

[jaytee716]

Perfect...

Almost done. Thanks so much for all of your help. What I ended up doing was to make a new status titled "unworked" in my status table and ran a query (because I have about 600 records) to label the blank records in my status column from the addition to my status table of "Unworked". This worked quite well and am pleased with everything that I have. Now I am working on my reporting. I have set up 4 reports so far (which I am happy with) but now am trying to figure out how to count records based on 2 columns in my main table and enter the counts into the new table that I made to track.

George, I'm going to put this into the Query section because it looks like (from my searching) that a lot of people are trying to find help on this as well.

Thank you again for everything!! You've been great and I am glad that there are people around like you. If your able to help with my next post, that would be great!! LOL.

Again, I appreciate everything.

Jim

 

[neileg]

There is a conceptual problem leaving the status blank and for this to have a meaning. What you have done will please the purists!

Well, almost. To get things perfect you should have a status table holding the StatusID and the status description. Store the ID in your main table. Then if you want to show "Not yet Worked" instead of "Unworked", you only need change one record, and not do a mass update.

 

[The_Doc_Man]

Never mind the purists, Neil. JT will have issues whether he is pure or very impure as long as a status field doesn't have "positive" statuses for every possible status. The idea of converting blank or null status to a default but non-empty value meaning "not there yet" is very important. Since there are special syntaxes for "IsNull" and "IsEmpty" and such, you could guess that in the past these value screwed up something.

Sort of the like the comedian whose schtick goes like this: Why do they have a sign on an airliner's door that says "DO NOT OPEN WHILE PLANE IS IN FLIGHT" ? Because sometime ago, somebody said, "I think I'll just go out this door to take a smoke."

Well, those special tests are there because people are sometimes negligent in assuring proper values in all fields for which you expect meaningful values. JT is assuring that and is to be applauded for thinking it might be necessary. Trust me, JT, it IS. And by keeping up that line of thinking, you won't go wrong.

 

[jaytee716]

Thank you...sorry I didn't post back sooner

I realized that having blank fields on something that I wanted to track was a big error on my side.

What I ended up doing was to run an append query and change all the blanks to "Unworked". I also made this the default value for any new records that were added and solved my problem.

LOL, well almost. So much has come up in the past 2 days with what my boss wants to see (he is fully aware that I had no access exper before working on this) so I am in the process of researching answers before I post a new thread and you guys think that I want you to write my DB .

Again, thanks for all the help. So far so good, but still miles to go!!!

Jim

 

[neileg]

Never mind the purists, Neil. JT will have issues whether he is pure or very impure as long as a status field doesn't have "positive" statuses for every possible status. The idea of converting blank or null status to a default but non-empty value meaning "not there yet" is very important. Since there are special syntaxes for "IsNull" and "IsEmpty" and such, you could guess that in the past these value screwed up something.

Yup, I know. I was being diplomatic!