A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2007 Table Issue



 
 
Thread Tools Display Modes
  #1  
Old February 16th, 2010, 02:38 PM posted to microsoft.public.access.tablesdbdesign
WSR[_3_]
external usenet poster
 
Posts: 15
Default Access 2007 Table Issue

I have developed a investment input form with look-up lists to restrict the
types of data to be entered.

The look-up lists come from subtables in the database.

When new records are saved the data is stored in a master table which I then
use to run Reports, Pivot Charts, Queries etc.

However, I'm finding that the master data table is in the same look-up list
format as the subtables that are used to populate the form.

In other words, the master data table has drop down choices for each record.

I'm finding that when I try to run a report the investment information is
lost. Instead of investment name I get results that show 1, 2, 3 etc.

My current work around is I export the master table data to Excel and then
re-import it as a flat file.

This is cumbersome and I'm hoping you can help.

Is there a way for the master table to store just data as a flat file and
not take on the look-up list format of the subtables used in the input form?

Sorry this is so long. I hope this makes sense. Your help is greatly
appreciated.

Thanks. (B^)-]=[


  #2  
Old February 16th, 2010, 05:15 PM posted to microsoft.public.access.tablesdbdesign
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Access 2007 Table Issue

WSR -

If your lookup tables are separate table in the database, then for your
queries or reports, you will need to join those table to the master table to
see the 'description' fields. You can do this in query view by adding the
lookup tables along with the master table. I suspect the joins will happen
automatically, as it sounds like you have the relationships set up. If any
of the fields with joined tables can be left empty, then you will need to
change the joins in the query to be an outer join - one that choose "All
Records from the Master Table...". Then choose the fields from the master
table that are not lookups for the report. Add in the 'description' fields
from the lookup tables, and you are set. You should be able to create one
master query, and then use it for other reports and queries.

--
Daryl S


"WSR" wrote:

I have developed a investment input form with look-up lists to restrict the
types of data to be entered.

The look-up lists come from subtables in the database.

When new records are saved the data is stored in a master table which I then
use to run Reports, Pivot Charts, Queries etc.

However, I'm finding that the master data table is in the same look-up list
format as the subtables that are used to populate the form.

In other words, the master data table has drop down choices for each record.

I'm finding that when I try to run a report the investment information is
lost. Instead of investment name I get results that show 1, 2, 3 etc.

My current work around is I export the master table data to Excel and then
re-import it as a flat file.

This is cumbersome and I'm hoping you can help.

Is there a way for the master table to store just data as a flat file and
not take on the look-up list format of the subtables used in the input form?

Sorry this is so long. I hope this makes sense. Your help is greatly
appreciated.

Thanks. (B^)-]=[


.

  #3  
Old February 16th, 2010, 06:01 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access 2007 Table Issue

On Tue, 16 Feb 2010 08:38:43 -0500, "WSR" wrote:

I have developed a investment input form with look-up lists to restrict the
types of data to be entered.

The look-up lists come from subtables in the database.

When new records are saved the data is stored in a master table which I then
use to run Reports, Pivot Charts, Queries etc.

However, I'm finding that the master data table is in the same look-up list
format as the subtables that are used to populate the form.

In other words, the master data table has drop down choices for each record.

I'm finding that when I try to run a report the investment information is
lost. Instead of investment name I get results that show 1, 2, 3 etc.

My current work around is I export the master table data to Excel and then
re-import it as a flat file.

This is cumbersome and I'm hoping you can help.

Is there a way for the master table to store just data as a flat file and
not take on the look-up list format of the subtables used in the input form?

Sorry this is so long. I hope this makes sense. Your help is greatly
appreciated.

Thanks. (B^)-]=[


You are another victim of Microsoft's misleading, nonstandard, limited "Lookup
Wizard".

Your main table APPEARS to contain the investment name. It doesn't! It
contains a number, a link to the lookup table. That simple fact is hidden from
view by the combo box.

The solution is to base your reports, pivots etc., not directly on the master
table, but on a Query joining the master table to the lookup tables. Select
the master table fields from the master table, the investment name field from
that lookup table, and so on.
--

John W. Vinson [MVP]
  #4  
Old February 18th, 2010, 06:23 PM posted to microsoft.public.access.tablesdbdesign
WSR[_3_]
external usenet poster
 
Posts: 15
Default Access 2007 Table Issue

John:

My Access 2007 instructor says "wow you got a response from him!"

Thanks for your feedback.

Much appreciated.

I will develop a query using the base tables records that feed the master
table and run my reports from that output, just like you mentioned.

Regards,

(B^)-]=[


"John W. Vinson" wrote in message
...
On Tue, 16 Feb 2010 08:38:43 -0500, "WSR" wrote:

I have developed a investment input form with look-up lists to restrict
the
types of data to be entered.

The look-up lists come from subtables in the database.

When new records are saved the data is stored in a master table which I
then
use to run Reports, Pivot Charts, Queries etc.

However, I'm finding that the master data table is in the same look-up
list
format as the subtables that are used to populate the form.

In other words, the master data table has drop down choices for each
record.

I'm finding that when I try to run a report the investment information is
lost. Instead of investment name I get results that show 1, 2, 3 etc.

My current work around is I export the master table data to Excel and then
re-import it as a flat file.

This is cumbersome and I'm hoping you can help.

Is there a way for the master table to store just data as a flat file and
not take on the look-up list format of the subtables used in the input
form?

Sorry this is so long. I hope this makes sense. Your help is greatly
appreciated.

Thanks. (B^)-]=[


You are another victim of Microsoft's misleading, nonstandard, limited
"Lookup
Wizard".

Your main table APPEARS to contain the investment name. It doesn't! It
contains a number, a link to the lookup table. That simple fact is hidden
from
view by the combo box.

The solution is to base your reports, pivots etc., not directly on the
master
table, but on a Query joining the master table to the lookup tables.
Select
the master table fields from the master table, the investment name field
from
that lookup table, and so on.
--

John W. Vinson [MVP]



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.