Access 2007 Table Issue
in [Tables]
|
Prev: Can we put two or three sets of data into one combobox?
Next: Access 97 won't save changes to form design view.
From: WSR on 16 Feb 2010 08:38 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^>)-]=[
From: Daryl S on 16 Feb 2010 11:15 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^>)-]=[ > > > . >
From: John W. Vinson on 16 Feb 2010 12:01 On Tue, 16 Feb 2010 08:38:43 -0500, "WSR" <wsr203(a)hughes.net> 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]
From: WSR on 18 Feb 2010 12:23
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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:vmjln51q3baajghsopv0i0fe9n09ngto1c(a)4ax.com... > On Tue, 16 Feb 2010 08:38:43 -0500, "WSR" <wsr203(a)hughes.net> 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] |