Simple frustrating question

Topic

New Reply

Hoping someone can clear the fog!

Trying to query 2 small tables with a 1 to many relationship between records in a database. Like to show (via file export) the data relationships in the tables so that the table with the many (up to 4)related records are displayed as fields along with the second tables fields.

Hope that makes sense and thanks in advance for any help!

Dan

Reply | Quote

Replies

I would suggest you use the Report Wizard and try as many types as you need to in order to get the result.

Reply | Quote

Are you trying to display a single record for each record in the one side of the relation with the fields in the many side shown in the same record? Or do you want to show repeated records from the one side for each matching record in the many side? The latter is a simple two-table query. The former is not relational, it’s spreadsheet thinking.

You need to give more information about the structure of the two tables and maybe an idea of the kind of a record you hope to display in order to get more specific help.

Reply | Quote

Spreadsheet thinking is what I’m after. These two files were created by a trade show barcode scanner and I want to combine them in a spreadsheet format.

Thought was Access could do the job with a query faster than sorting and manually combining the two tables in Excel.
Table 1 has 1015 unique records (related to 1 or more records in table 2).
Table 2 has 1457 records (related to only record in table 1).

Reply | Quote

Related how? Do your table1 have a key field that’s a foreign key in table2? You need to include that information. If each of the tables has only one field, or one field of barcode info and an autonumber, there isn’t any way to relate them unless the same info is contained in both tables.

Reply | Quote

The tables have a common field and I’ve already linked them using a query. Here is layout:
Table 1 (1015 records)- BadgeID FName LName …
Table 2 (1457 records)- BadgeID ActionCode

I just can’t get the Query to show the ActionCodes (12 unique and up to 4 per record in table 1) in columns “added” to Table 1. Like this:
Query 1- BadgeId FName LName … ActionCode1 ActionCode2 ActionCode3 ActionCode4
1 Frank Smith Send1Catalo Send2Catalo Send2Catalo

The above files come that way from the scanner and the sample data says to send a total of 5 catalogs to the guy.

Thanks for your questions. I’m getting the feeling this sort of task is not really what Access is designed to do but still think there should be a way to display records from table 2 in columns along with the records from table 1.

Thanks again!

Reply | Quote

Try playing with a crosstab query based on both tables.

Reply | Quote